To manage the flow of information from one table (A) to another table (B), the table that holds the information, A, must make it available to other tables, such as B. There are various issues that must be dealt with:
These problems are solved by specifying a particular column or a group of columns as the "key" of the table. Such a column or group of columns is called the primary key.
In a relational database, which is the case for most of the databases you will be creating, each table should have at least one primary key. As an example, a primary key on a Customers table of a bank database can be set on a Bank Account field because each customer should have a unique bank account number.
Once you have decided that a table will have a primary key, you must decide what type of data the field(s) will hold. If you are building a table that can use a known and obvious field as unique, an example would be the shelf number of a library, you can set its data type as char (or nchar) or varchar (or nvarchar) and make it a primary key. In many other cases, for example if you cannot decide on a particular field that would hold unique information, an example would be customers Contact Name, you should create your own unique field and make it the Primary Key. In most cases when you hesitate, the data type of the primary key should be an integer, usually int.
Among the rules you must follow, a table can have only one primary key. If you use one column as the primary key, that column cannot hold a NULL value.
To create a primary key in SQL Server Management Studio, create a column and specify its data type:
Here is an example:
To create a primary column using SQL, the primary thing to do is, on the right side of the column definition, type PRIMARY KEY. Here is an example:
CREATE TABLE Persons ( PersonID int PRIMARY KEY NOT NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL );
In the SQL, you can give a specific name to a primary key. To do this, you can first create the column. Then, somewhere before the closing parenthesis of the table, specify the primary key column using the following formula:
CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)
In this formula, the CONSTRAINT keyword and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName placeholder, enter the name you want to give to the primary key. In the parentheses of the PRIMARY KEY expression, enter the name of the column that will be used as the primary key. Here is an example:
CREATE TABLE Persons ( PersonID int NOT NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL, CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID) ); GO
By convention or tradition, the name of the primary starts with PK_ followed by the name of the table. Here is an example:
CREATE TABLE Persons ( PersonID int NOT NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL, CONSTRAINT PK_Persons PRIMARY KEY(PersonID) ); GO
A field that is the primary key of a table must always have a value for each record. This means that NULL and the empty string are not allowed. If you fail to provide a value for a the primary key column, you would receive an error. Here is an example:
CREATE DATABASE Exercise3; GO USE Exercise3; GO CREATE TABLE Genders ( GenderID int not null PRIMARY KEY, Gender nvarchar(20) ); GO INSERT INTO Genders(Gender) VALUES(N'Male'); GO
This would produce:
The first detail to which you must pay attention is the data type of the primary key column.
One of the most common values of a primary key is an integer. In most cases, when creating such a column, you should assign it the integer data type. If you know for sure the limits of the primary key, you can apply a tinyint or a small int. Here are examples:
USE Exercise1; GO CREATE TABLE Personnel.Genders ( GdrNbr tinyint, Gender nvarchar(20), CONSTRAINT PK_Genders PRIMARY KEY(GdrNbr) ); GO CREATE TABLE Personnel.MaritalsStatus ( StatusCode smallint, MaritalStatus nvarchar(32), CONSTRAINT PK_MaritalStatus PRIMARY KEY(StatusCode) ); GO
When performing data entry on a field whose primary key is an integer, provide the desired value but make sure the value is unique among the other values of the same column.
Besides integers, strings are among the most used types for a primary key. You have a choice between the char, the varchar and their variances. You should never usse the text data type as a primary key. When performing data entry, make sure you provide a value in single-quotes for each record.
There are a few rules and suggestions you should follow creating a primary key:
Continuing with our bank database, imagine a customer comes to the bank to deposit money. We already established that it would be redundant to create a new account every time the customer comes to perform a transaction. Instead, you would get the customer's information from his or her account, provide that information to the table used to process transactions. As we described earlier, the account table should be able to provide its data to the other tables that would need that data. To make this flow of information possible from one table to another, you must create a relationship between them.
To make it possible for a table B to receive data from a table A, the table B must have a column that represents the table A. This columns acts as an "ambassador" or a link. As a pseudo-ambassador, the column in the table B almost doesn't belong to that table: it primarily allows both tables to communicate. For this reason, the column in the table B is called a foreign key.
A foreign key is a column on a table whose data is coming from another table. Unlike a primary key that must be unique, a table can have as many foreign keys as possible since each foreign key would relate to a different table. Still, Microsoft recommends a maximum of 253 foreign keys per table.
To create a foreign key in the Table Design window, in the table that will receive the key, simply create a column with the following rules:
Obviously, in order to have information flowing from one table to another, the table that holds the primary information must have been created. You can create it before or after creating the other table, as long as you haven't established any link between both tables, it doesn't matter what sequence you use to create them.
The table that contains a primary key and that holds the information that another table would use is called the primary table or the parent table. The table that will receive the information from the other table is called the foreign table or the child table.
To create a foreign key in a table:
You can also create a foreign key in the SQL. The basic formula to use is:
FOREIGN KEY REFERENCES ParentTableName(ForeignKeyCcolumn)
The FOREIGN KEY expression and the REFERENCES keyword are required. In the ParentTableName placeholder, enter the name of the primary table that holds the information that will be accessed in the current table. In the parentheses of ParentTableName, enter the name of the primary column of the parent table. Here is an example:
CREATE TABLE Persons ( PersonID int PRIMARY KEY NOT NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL, GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID) ); GO
Based on the above technique, notice that the foreign key does not have an object name as we saw for the primary key. If you do not specify a name for the foreign key, the SQL interpreter would automatically create a default name for you. Otherwise, to create a name, after creating the column, enter the CONSTRAINT keyword followed by the desired name and continue the rest as we saw above. Her is an example:
CREATE TABLE Persons ( PersonID int PRIMARY KEY NOT NULL, FirstName varchar(20), LastName varchar(20) NOT NULL, GenderID int NULL CONSTRAINT FKGenders FOREIGN KEY REFERENCES Genders(GenderID) ); GO
You can also create a foreign key as its own constraint, which is another technique to name a foreign key. To assign a desired name to a foreign key, you must create it as a constraint. To do this, after defining the column that holds the foreign key before the end of defining the table, create a constraint using the following formula:
CONSTRAINT Name FOREIGN KEY(Foreign Key) REFERENCES Parent(Foreign Key)
Here is an example:
CREATE TABLE Genders ( GenderID int NOT NULL, Gender nvarchar(20) NOT NULL, CONSTRAINT PK_Genders PRIMARY KEY(GenderID) ); GO CREATE TABLE Persons ( PersonID int PRIMARY KEY NOT NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL, GenderID int, Comments nvarchar(max), CONSTRAINT FK_Genders FOREIGN KEY(GenderID) REFERENCES Genders(GenderID) ); GO
To create a relationship between two tables
A diagram is a window that visually displays the relationships among tables of a database. To create a diagram:
As mentioned already, when creating the records of a table, assign the appropriate value for the primary key. Each value must be unique.
The value of a foreign key column must exist in the primary key of the table it represents. If the value cannot be found in the parent table, you would receive an error. If you need to use a value that is not in the parent table, first created it there, and then return to the child table to create the record.
NULL should be avoided in any column that is involved in a relationship. When creating the records of a table that has a primary key, if you know that the child table will have records that do not have a value in the parent table, in the parent table, create a "dummy" record that has a value such as N/A, Not Available, Unknown, or something like that.
There are many issues you need to be concerned about in a relational database. Some issues are related to the columns of tables and some issues have to do with records. This means that you should always think about structures of tables when you decide to either add new columns or delete records.
If you decide to delete a table, first check if it is involved in a relationship. If so, is it the parent (is it providing its records to another table?) or the child (is one of its columns receiving values from a parent table?)? If a table is a child, you can easily delete it using any of the techniques we know already. If a table is a parent, you will receive an error.
As an alternative, you can add a column, and then use the CONSTRAINT formula to define the primary key. Here is an example:
ALTER TABLE Employees ADD EmployeeNumber int not null CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber); GO
Just as you add a primary key to an already created table, you can also add a new column that is a foreign key. Consider the following table named Persons:
CREATE TABLE Genders ( GenderID int not null PRIMARY KEY, Gender nvarchar(20) ); GO CREATE TABLE Persons ( PersonID int PRIMARY KEY NOT NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL ); GO
The formula to add a foreign key to an existing table is:
ALTER TABLE TableName ADD NewColumnName DataType Options FOREIGN KEY REFERENCES ParentTableName(ColumnNameOfOtherTable); GO
Here is an example of adding a foreign key to the above Persons table:
ALTER TABLE Persons ADD GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID); GO
So far, we have used primary keys made of only one column. Sometimes, that one column is not enough to uniquely identify a record. An alternative is to use more than one column. A composite key is a primary key that uses more than one column.
To visually create a composite key, while the table is being designed, click the row header of one of the columns. Press and hold Ctrl (or Shift, if the columns are in a range). Then click the row header of each of the columns that will be members of the primary key (or the row header of the column at the end of the range). After making the selections, release Ctrl (or Shift). Then:
In both cases each of the columns that participate in the primary key would display the icon on its row header:
In the same way, you can involve as many columns as you want. One of the ways to create a composite key is to use the foreign keys that represent the primary keys of other tables.
To create a composite primary key in SQL, in the parentheses of the primary key, include the names of the columns separated by commas. Here is an example:
CREATE DATABASE InformationTechnologyJournal; GO USE InformationTechnologyJournal; GO CREATE SCHEMA Publishing; GO CREATE SCHEMA Authorship; GO CREATE TABLE Authorship.Reviewers ( ReviewerNumber nchar(6) not null, FirstName nvarchar(24), MiddleName nvarchar(24), LastName nvarchar(24), Citizenship nvarchar(40), Constraint PK_Reviewers Primary Key(ReviewerNumber) ); GO CREATE TABLE Publishing.Affiliations ( AffiliationCode nchar(5) not null, AffiliationName nvarchar(60), Constraint PK_Affiliations Primary Key(AffiliationCode) ); GO CREATE TABLE Authorship.ReviewersAffiliations ( ReviewerNumber nchar(6) not null, AffiliationCode nchar(5) not null, Constraint PK_ReviewersAffiliations Primary Key(ReviewerNumber, AffiliationCode) ); GO
A candidate key is a column that participates in a primary key. If the primary key contains only one column, that column is a candidate key. If the primary key is made of more than one column, each one of the columns is a candidate key.