What Are Foreign Keys in SQL Databases?
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
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.
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.
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
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.