SQL FOREIGN KEY constraint

FOREIGN KEY constraint

Assume there is the departments table with the columns department_id INT PRIMARY KEY and department_name VARCHAR(20) NOT NULL:

To create the employees table, the columns include employee_id INT PRIMARY KEY, name VARCHAR(60), and department_id INT. An employee's department affiliation is limited to existing departments, ensuring that all department_id values in the employees table align with corresponding values in the departments table.

To establish this connection between the two tables, department_id in the employees table can be designated as a foreign or referencing key. The database management system will oversee the data permissible in the employees table's foreign key column.

 Note that the FOREIGN KEY constraints can only reference tables within the same database.

To mark a field or a group of fields as a foreign key, use the FOREIGN KEY constraint and create the table employees. In the query, the REFERENCES keyword specifies the table and the primary key column or columns (in parentheses) with unique values to which the foreign key points. The structure and data type of the primary and foreign keys must be the same. The table that the foreign key points to is called the 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, insert a tuple (1, 'Ann Riding', 4) in employees will cause an error because the table departments does not have a row with such a department.

With the query above, there is a named foreign key created. It is good practice to use only named foreign keys because, in some SQL dialects (including MySQL), unnamed ones may cause trouble in the future. However, there is still an option to 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 adding some data to employees, and now the connections between the departments and employees look like this:

If data is updated or deleted in the table departments, there should be a change in the corresponding data in the table employees. The result of these changes depends on the referential actions. Make sure to specify how SQL should act if there is a data change using ON DELETE and ON UPDATE actions.

There are several options to specify data:

• 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 there is an attempt 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 in case of creating the table employees with one of the queries above, delete and update actions in the table departments will be forbidden.

Consider changing the previous 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, use the ALTER TABLE ADD FOREIGN KEY statement.

If the employees table is created without a foreign key, 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);

To create a table with multiple foreign key columns, specify multiple columns in parentheses. Also, add ON UPDATE and ON DELETE actions to these queries 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, it is required 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 creating 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 the table, and the foreign key name will be a part of this query.