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:

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:

From the Object Explorer or the Server Explorer, open the child table in Design View

Right-click anywhere in the table and click Relationships...

In the Foreign Key Relationships dialog box, click Add

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:

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:

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: