Foreign keys allow database administrators to easily identify the different connections that exist within an SQL database management system.

SQL performs mathematical operations on data within a database management system. These databases contain different tables that each store data on a specific entity. If you have a car rental database, an entity (or table) in that database will be customers (which will store all the personal data on each customer).

These database tables contain rows and columns, where each row hosts a record and each column holds attribute-specific data.

In a database management system, each record (or row) should be unique.

Primary Keys

Though the stipulation is that each record in a table should be distinct, this isn’t always the case. Continuing with the car rental database example, if the database contains two customers that each have the name “John Brown”, a John Brown could be expected to return a Mercedes-Benz that he didn’t rent.

Creating a primary key will mitigate this risk. In an SQL database management system, a primary key is a unique identifier that distinguishes one record from another.

Therefore, every record in an SQL database management system should have a primary key.

Using Primary Keys in a Database

To include primary keys in a database management system using SQL, you can simply add it as a normal attribute when creating a new table. So the customers' table will contain four attributes (or columns):

  • CarOwnerID (which will store the primary key)
  • FirstName
  • LastName
  • PhoneNumber

Related: How to Create a Table in SQL

Now every customer record that enters the database will have a unique identification number, as well as a first name, last name, and phone number. The phone number isn’t unique enough to be a primary key, because though it is unique to one person at a time, a person can easily change their number, meaning it would now belong to someone else.

A Record With a Primary Key Example

        /* creates a new record in the customers table */
INSERT INTO Customers VALUES
('0004',
'John',
'Brown',
'111-999-5555');

The SQL code above will add a new record to the pre-existing Customers table. The table below shows the new customer table with the two John Brown records.

Customers table

The Foreign Key

Now you have primary keys that uniquely distinguish one car renter from another. The only problem is that, in the database, there is no real connection between each John Brown and the car that he rents.

Therefore, the possibility of making a mistake still exists. This is where foreign keys come into play. Using a primary key to solve the problem of ownership ambiguity is only achievable if the primary key doubles as a foreign key.

What Is a Foreign Key?

In an SQL database management system, a foreign key is a unique identifier or a combination of unique identifiers that connect two or more tables in a database.

Of the four SQL database management systems in existence, the relational database management system is the most popular one. When deciding which table in a relational database should have a foreign key, you should first identify which table is the subject and which is the object in their relationship.

Going back to the car rental database, to connect each customer to the correct car you’ll need to understand that a customer (the subject) rents a car (the object). Therefore, the foreign key should be in the cars table.

The SQL code that generates a table with a foreign key is slightly different from the norm.

Creating a Table With a Foreign Key Example

        /* creates a new cars table in the car rental database */
CREATE TABLE Cars
(
LicenseNumber varchar(30) NOT NULL PRIMARY KEY,
CarType varchar(30) NOT NULL,
CustomerID varchar(30) FOREIGN KEY REFERENCES Customers(CustomerID)
);

As you can see in the code above, a foreign key has to be explicitly identified as such, along with a reference to the primary key that is being connected to the new table.

Related: The Essential SQL Commands Cheat Sheet for Beginners

To add a record to the new table, you’ll need to ensure that the value in the foreign key field matches the value in the primary key field of the original table.

Adding a Record With a Foreign Key Example

        /* creates a new record in the cars table */
INSERT INTO Cars VALUES
('100012',
'Mercedes-Benz',
'0004');

The code above creates a new record in the new Cars table, producing the following result.

Cars Table

Cars table

From the table above, you can identify the correct John Brown that rents a Mercedes-Benz by the foreign key in the record.

Advance Foreign Keys

There are two other ways to use a foreign key in a database.

If you look back on the definition of a foreign key above, you’ll find that it says a foreign key can be a unique identifier or a combination of unique identifiers.

Going back to the car rental database example, you’ll see that creating a new record (of the same car) each time a customer rents that car, defeats the purpose of the Cars table. If the cars are for sale and are sold to a single customer once, the existing database is perfect; but given that the cars are rentals there's a better way to represent this data.

Composite Keys

A composite key has two or more unique identifiers. In a relational database, there’ll be instances when the use of a single foreign key won't sufficiently represent the relationships that exist within that database.

In the car rental example, the most practical approach is to create a new table that stores the rent details. For the information in the car rental table to be useful, it has to connect to both the car and the customer tables.

Creating a Table With Composite Foreign Keys

        /* creates a CarRental table in the car rental database */
CREATE TABLE CarRental
(
DateRented DATE NOT NULL,
LicenseNumber varchar(30) NOT NULL FOREIGN KEY REFERENCES Cars(LicenseNumber),
CustomerID varchar(30) NOT NULL FOREIGN KEY REFERENCES Customers(CustomerID),
PRIMARY KEY (DateRented, LicenseNumber, CustomerID)
);

The code above depicts an important point; though a table in an SQL database can have more than one foreign key, it can only have a single primary key. This is because there should only be one unique way to identify a record.

It's necessary to combine all three attributes in the table to have a unique key. A customer can rent more than one car on the same day (so CustomerID and DateRented isn’t a good combination) more than one customer can also rent the same car on the same day (so LicenseNumber and DateRented isn’t a good combination).

However, the creation of a composite key that tells which customer, what car, and on what day makes an excellent unique key. This unique key represents both a composite foreign key and a composite primary key.

Foreign Primary Keys

Oh yes, foreign primary keys do exit. Though there’s no official name for it, a foreign key can also be a primary key in the same table. This happens when you create a new table that contains specialized data about an existing entity (or record in another table).

Say Fred (who works at the car rental company) is in the company’s database under the employee table. After a few years, he becomes a supervisor and gets added to the supervisor table.

Fred is still an employee and will still have the same id number. So Fred’s employee id is now in the supervisor table as a foreign key which will also become a primary key in that table (as it makes no sense to create a new id number for Fred now that he's a supervisor).

Now You Can Identify Foreign Keys In SQL Databases

Foreign keys connect different tables within an SQL database. From this article, you can see what a foreign key is, how it works, and why it's important to have them in a database. You also understand the basic, and even more complex, forms of foreign keys.

If you think foreign keys are interesting, you’re going to have a field day when you start using the project and selection operations to query your SQL databases.