FOREIGN KEY constraint

You already know how to create a primary key and how to create a database with several tables in it. If you want to create a truly powerful database, you should know how to link the tables together.

Let's consider an example: we want to store information about employees and work departments. We can create one table with all data, but if we open a new department and don't hire anyone for it immediately, how would we store information about it? It is easier if we create two different tables: one with employees and one with departments. Here, we face another challenge: how to connect data in these tables? How to avoid problems if we add an employee with a non-existing department name? In this topic, you will learn how to use the foreign key constraint to connect two tables and manage the data stored there.

FOREIGN KEY constraint

Assume that we already have a table departments with columns department_id INT PRIMARY KEY and department_name VARCHAR(20) NOT NULL. It looks as follows:

We want to create a table employees with columns employee_id INT PRIMARY KEY, name VARCHAR(60) and department_id INT. An employee can only work in an existing department, so all values in the column department_id in the table employees should have a corresponding department_id value in the table departments, so these two columns should be connected.

To create a link between these two tables, we can mark the department_id in employees as a foreign key or referencing key. The database management system will control the data that can be stored in the foreign key table employees for us.

To mark a field or a group of fields as a foreign key, we can use the FOREIGN KEY constraint and create the table employees. In the query, keyword REFERENCES specifies the table and the primary key column or columns (in parentheses) with unique values that the foreign key points to. The structure and data type of the primary key and the foreign key must be the same. The table that the foreign key points to is called referenced or parent table. Here is what the query will look like:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(60) NOT NULL, 
    department_id INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
); 

After the query execution, the table employees becomes a child table, that is, a table containing the foreign key.

Now if we try to insert a tuple (1, 'Ann Riding', 4) in the table employees, we will get an error because the table departments doesn't have a row with such department in it.

With the query above, we created a named foreign key. It is good practice to use only named foreign keys because in some SQL dialects (including MySQL), unnamed foreign keys may cause trouble in the future. However, you can still create a table with an unnamed foreign key:

CREATE TABLE employees (
    employee_id int PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Referential actions

Assume that we add some data to the table employees, and now the connections between our tables departments and employees look like this:

If we update or delete data in the table departments, we should change the corresponding data in the table employees. The result of these changes depends on the referential actions. We can specify how SQL should act if we change the data using ON DELETE and ON UPDATE actions.

We can specify different actions:

• CASCADE: if a row in the parent table is deleted or updated, all matching rows will be deleted or updated automatically;

• SET NULL: if a row in the parent table is deleted or updated, all matching foreign key values in the child table will be set to NULL;

• RESTRICT: if we try to update or delete a row in the parent table, the operation will be rejected;

• SET DEFAULT: if a row with the corresponding value is deleted or updated, the foreign key value in the child table will be set to the default value;

• NO ACTION: this keyword can mean different actions depending on a dialect. In MySQL, it is equivalent to the RESTRICT keyword, so if we create the table employees with one of the queries above, delete and update actions in the table departments will be forbidden.

Let's change our SQL query to set actions ON UPDATE and ON DELETE:

CREATE TABLE employees (
    employee_id int PRIMARY KEY,
    name VARCHAR(60) NOT NULL, 
    department_id INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
); 

Add FOREIGN KEY to the existing table

To add a foreign key to the existing table, you can use the ALTER TABLE ADD FOREIGN KEY statement.

If we created our table employees without a foreign key, we can add it with a simple SQL query:

ALTER TABLE employees
ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);

To add a named foreign key or a FOREIGN KEY constraint to multiple columns, use the ALTER TABLE ADD CONSTRAINT statement:

ALTER TABLE employees
ADD CONSTRAINT fk_department FOREIGN KEY (department_id)
REFERENCES departments(department_id);

If you want to create a table with multiple foreign key columns, just specify multiple columns in the parentheses. You can also add ON UPDATE and ON DELETE actions to these queries if you want to specify them.

Drop foreign key

To delete a foreign key, use the ALTER TABLE DROP FOREIGN KEY statement:

ALTER TABLE employees 
DROP FOREIGN KEY fk_department;

In MySQL, we have to know the name of the foreign key to delete it. This is one of the reasons why using named foreign keys is good practice, but even if you create an unnamed foreign key constraint, DBMS will generate the name automatically.

To get the foreign key name in this case, use the following syntax:

SHOW CREATE TABLE table_name; 

This query will show the autogenerated CREATE TABLE query for your table, and the foreign key name will be a part of this query.

Conclusion

A FOREIGN KEY constraint is a useful SQL feature that allows you to cross-reference related data across the tables. Now you know how it works, how to create a table with a foreign key, and how to add/delete a foreign key from an existing table.