SQL References - Constraints Part 2

REFERENCES is another constraint that you should use during table creation. This is a bit more complex than other constraints we discussed in the last post, so it is worth having its own article.

REFERENCES will help to establish connections between tables and enable data to be linked and queried efficiently.

In the above example, we have 2 tables. Customers and Orders. The two tables are connected through the CustomerID column.

Orders is the referencing table. The value of the referencing column (CustomerID) must appear in the referenced table (Customers) as well.

Self-referencing

Referencing and referenced tables can be the same table. In such a case, we call it self-referencing.

This commonly represents hierarchical relationships, such as employees and their managers. The managers are also employees so we want to ensure that they are part of the Employees table. This solution avoids the need for a separate table for relationships.

Circular references

A circular reference occurs when two tables reference each other, the two are depending on each other.

• Orders references Shipments through ShipmentID.

• Shipments references Orders through OrderID.

Circular references can lead to complex logic and misunderstandings so generally try to avoid it if possible.

Referential Actions

You can define two subclauses that will take action based on the database changes in the referenced table. These clauses have self-explanatory names:

ON DELETE - action takes place when a row is deleted from the referenced table.

ON UPDATE - action takes place when a row in the referenced table is updated.

CASCADE

ON DELETE CASCADE - When a row is deleted in the referenced table, all rows in the referencing table that depend on it are automatically deleted.

If a row in ReferencedTable with ID = 1 is deleted, all rows in ReferencingTable with RefID = 1 are also deleted automatically.

ON UPDATE CASCADE - When a row in the referenced table is updated, the foreign key in the referencing table is automatically updated to match the new value.

If a row in ReferencedTable has its ID updated from 1 to 2, all rows in ReferencingTable with RefID = 1 are automatically updated to RefID = 2.

SET NULL

This option will change the values in the referencing table to a NULL. Column must be nullable.

ON DELETE SET NULL - When a row is deleted in the referenced table, the foreign key in the referencing table is set to NULL.

ON UPDATE SET NULL - When a row in the referenced table is updated, the foreign key in the referencing table is set to NULL.

If a row in ReferencedTable with ID = 1 is updated to ID = 2, all rows in ReferencingTable with RefID = 1 will have their RefID set to NULL.

This can be useful for example with projects. Updating the primary key for a project in the Projects table sets the related project references in the Tasks table to NULL.

SET DEFAULT

The SET DEFAULT option will change the values in the referencing table to the default value of that column if there is a given DEFAULT. Each referencing column can have its default in its table.

NO ACTION /RESTRICT

The delete or update operation for the table is rejected if there is a related foreign key value in the referenced table.

For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION.

We have a really important difference between NO ACTION and RESTRICT:

ON DELETE RESTRICT - Enforcement is immediate. The database raises an error as soon as you attempt to delete the row.

ON DELETE NO ACTION - Enforcement is deferred until the end of the SQL transaction. If the transaction is complete and the reference still exists, the database raises an error.

The NO ACTION solution allows intermediate steps to run through. The constraint can be violated if it is resolved until the end of the transaction.

For example, consider this:

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID) ON DELETE NO ACTION
);

You want to delete the HR department (DeptID = 1) and reassign all employees in that department to the Finance department (DeptID = 2).

The foreign key constraint is deferred until the transaction ends, allowing for intermediate steps that temporarily violate the constraint.