What Is a Foreign Key in SQL?

What does a foreign key do in SQL? Why would you need one? Learn the ABCs of foreign keys in five minutes.

Foreign keys are a central concept in SQL databases; they allow us to enforce data consistency. Usually they work with primary keys to connect two database tables, like a virtual bridge. All SQL developers need to know what SQL foreign keys are, how they work, what data values are allowed in them, and how they’re created.

What Are SQL Foreign Keys?

When designing a database, it often happens that an element can’t be fully represented in just one table. In that case, we use a foreign key to link both tables in the database.

Suppose we have a database with country data stored in a table called country. We also want to represent the world’s major cities in this database. Initially, we think of creating a single table called city to store the city name, a numeric city_id, and the country where this city is located. For this last attribute, we add a column called country_id that references a record in the country table. Thus, part of the city information – the name of the country where the city is located – is actually stored in the country table. We can say that these tables are related.

Below, we can see the SQL to create the country and city tables:

CREATE TABLE country (
    country_id        INTEGER,
    name              VARCHAR(50),
    population        INTEGER
);

CREATE TABLE city  (
    city_id            INTEGER,
    name               VARCHAR(50),
    country_id         INTEGER
);

Notice that city has a column called country_id that is the link between both tables. The value in the column city.country_id should refer to a valid country_id in the country table. Using the city.country_id value, we can join the city table with country and find the record associated with any city. So far, so good.

However, the database doesn't know that both tables are related. Moreover, the database will allow a user to put an invalid value in city.country_id (e.g. something that points to a non-existent record in the country table). A foreign key constraint will inform the database about the relation between the tables.

A foreign key constraint is a database constraint that binds two tables. Before we can create a foreign key on the table city, we need a primary key or a unique constraint on the country table. In the code below, we drop and re-create both tables – this time, defining the primary and foreign keys:

-- We first create the country table with a primary key
DROP TABLE country;
CREATE TABLE country (
    country_id        INTEGER,
    name              VARCHAR(50),
    population        INTEGER,
    PRIMARY KEY       (country_id)
);

-- We create the city table with a primary key and a foreign key
DROP TABLE city;
CREATE TABLE city  (
    city_id            INTEGER,
    name               VARCHAR(50),
    country_id         INTEGER,
    PRIMARY KEY        (city_id),
    FOREIGN KEY        (country_id) REFERENCES country(country_id)
)

Once we created the FOREIGN KEY, as we can see in the previous box, the database will verify that the values stored in city.country_id are valid country.country_id values.

Before continuing to the next section, I would like to mention the course The Basics of Creating Tables in SQL, where you can learn topics related to tables, foreign key constraints, and primary key constraints. I’d also like to suggest the article What is a Primary Key in SQL?, which provides an interesting entry-level explanation about primary keys.

Going Deeper with Foreign Key Constraints

Once we have the foreign key constraint between the country and city tables, we can think of country as the parent table and city as the child table. Behind all foreign key validations there is a single rule: The database must ensure that every record in the child table always refers to a valid record in the parent table. There can be several scenarios where this validation is executed:

• If an INSERT INTO city (the child table) is executed, the database must validate that the value of the city.country_id column refers to an existing record in country (the parent table)
• If a DELETE FROM country (the parent table) is executed, the database must validate that the city table doesn’t have any records pointing to the country_id being deleted.
• If an UPDATE for the column country_id on either table (child or parent) is executed, the database must avoid having a record in city (the child side) with a value in country_id that doesn’t exist in the country table (the parent side).

Let’s see some examples. Suppose we initially have an empty database and we insert these two records. Both INSERTs will be valid:

INSERT INTO country (country_id, name, population) 
            VALUES (1,’United States’,328000000 );
Query returned successfully: one row affected

INSERT INTO city (city_id,name,country_id)
            VALUES (100,’New York’,1 );
Query returned successfully: one row affected

However, if we try to insert the following record referring to the country_id 2, we get the following error:

INSERT INTO city (city_id,name,country_id)
            VALUES (101,’Paris’,2 );

ERROR:  Insert on table «city» violates foreign key «fk_city_country»
DETAIL: The key (country_id)=(2) is not present in the table «country».

In the case of a DELETE command, there is a ON DELETE CASCADE clause that can be used when the foreign key is created. If we used the ON DELETE CASCADE clause in the foreign key definition, then any DELETE on the parent table (country) will fire automatic DELETES on the child records instead of returning a validation error. In other words, if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.

There are also other clauses like RESTRICT, NO ACTIONS, or SET NULL that can be used when defining a foreign key. The next CREATE TABLE example shows how to create the table city with the CASCADE DELETE activated.

DROP TABLE city;
CREATE TABLE city  (
   city_id        INTEGER,
   name           VARCHAR(50),
   country_id     INTEGER,
   PRIMARY KEY    (city_id),
   FOREIGN KEY    (country_id) REFERENCES country(country_id) ON DELETE CASCADE 
)

There is an interesting article about foreign keys where you can see several more examples; have a look for additional info.

Multi-Column Foreign Keys

In the same way that primary keys can be made up of multiple columns, foreign keys in SQL can also be made up of multiple columns.

Suppose there is a brand of luxury watches that maintain records of the owners of all the watches they produce. Every kind of watch model is identified by a model name and a sub-model code. We can see this in the following table, watch:

Here, the primary key for the table watch is a multi-column key formed by the Model and Sub_model columns.

The database has another table called owner that stores information on each watch owner’s name, the purchase date, and the watch model and sub-model:

The following SQL code is used to create the primary key on the table watch and the foreign key on the table owner.

-- We first create the watch table with the primary key
CREATE TABLE watch (
    Model          VARCHAR(15),
    Sub_model      VARCHAR(15),
    Price          NUMERIC,
    PRIMARY KEY (model, sub_model)
);
-- We create the owner table with a multi-column foreign key
CREATE TABLE owner ( 
    Owner_name     VARCHAR(50),
    Model          VARCHAR(15),
    Sub_model      VARCHAR(15),
    Purchase_date  DATE,
    PRIMARY KEY    (Owner),
    FOREIGN KEY    (Model, Sub_model) REFERENCES watch(model, sub_model)
);

Before closing this section, there’s one more article I’d like to suggest on table creation in SQL. It’s good for those readers who want to review basic table creation concepts.

There’s More to Learn About SQL’s Foreign Keys

In this article, we explained how foreign keys work in SQL and how to create them. We also showed some SQL codes for foreign key creation. And we explored some possible errors we can get when working with foreign keys.

One kind of foreign key we didn’t cover are those that point to their own table. For example, we could have an employee table with the columns emplid and boss_emplid, where emplid is the primary key and boss_emplid is the foreign key.

Finally, I would like to suggest the LearnSQL track Create Database Structure, which includes a section on primary and foreign key constraints in a database. It’s a good place to practice the new concepts we’ve talked about.