Home

Data Relationships: The Foreign Key
 

Introduction

Continuing with our car rental database, imagine a customer comes to rent a car. We already established that it would be redundant to create new car information every time you process a new customer order. Instead, you would get the car's information from the table that holds data for the cars, and provide that information to the table used to process orders. As we described earlier, the car 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 does not 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.

Creating a Foreign Key in the Table Design View

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:

  • The column should have the same name as the primary column of the table it represents (but this is not a requirement)
  • The column must (this is required) have the same data type as the primary column of the table it represents

Here is an example of a column named GenderID that is a foreign key:

Foreign Key

Obviously in order to have information flowing from one table to another, the table that holds the primary information must be created. You can create it before or after creating the other table, as long as you have not established any link between both tables, it does not 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.

Creating a Foreign Key in the Relationships Dialog Box

To create a foreign key in a table:

  1. From the Object Explorer or the Server Explorer, open the child table in Design View
  2. Right-click anywhere in the table and click Relationships...
     
    Foreign Key
  3. In the Foreign Key Relationships dialog box, click Add
  4. A default name would be suggested to you. You can accept or change it. To change the name of the foreign key, on the right side, expand Identity and edit the string in the (Name) field:
     
    Foreign Key
  5. If necessary, in the same way, you can create other foreign keys by clicking Add. To delete an existing foreign key, first select it under Selected Relationships and click Delete.
    Once you are ready, click Close

Creating a Foreign Key in SQL

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 identity(1,1) PRIMARY KEY NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);

The Foreign Key Constraint

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 identity(1,1) PRIMARY KEY NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID int NULL CONSTRAINT FKGenders
                       FOREIGN KEY REFERENCES Genders(GenderID)
);
 
Home Copyright © 2008-2016, FunctionX, Inc.