Referential integrity

When used properly, referential integrity is a powerful tool to help you keep your tables in order. The two main uses are:

1. to stop you deleting rows that are referenced elsewhere

2. to allow you to delete a row in table A and have the DBMS automatically delete all other rows in other tables that reference that row

We could rewrite our Orders table definition to mark Customer as a foreign key of the ID field in the Customers table by doing this:

CREATE TABLE Orders (ID INT, Customer INT, Purchase CHAR(255), FOREIGN KEY (Customer) REFERENCES Customers(ID));

With that in place, rows in the Orders table are explicitly tied to customers in the Customers table. Note that some DBMSs may require you to make the foreign key (Customer, in the example) indexed. For example, try executing the following query:

mssql_query("DELETE FROM Customers WHERE ID = 1;");

With the foreign key in place, the default action is to stop people from making a call like this as it would leave rows in the Orders table pointing to a customer that doesn't exist. So, if you try that query, you should get an error like this one: "DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Orders__Customer__76969D2E'. The conflict occurred in database 'phpdb', table 'Orders', column 'Customer'."

The constraint name (FK__Orders__Customer__76969D2E) is randomly generated when the foreign key link is created, but what it is trying to say is that you attempted to delete a key that has rows relying upon it, which is a no-no. However, this has amply demonstrated the first helpful use of referential integrity: to stop you deleting rows that are referenced elsewhere.

By modify the query again, we can get the DBMS to perform the opposite action - enforce integrity by deleting rows that are referenced. Here is the new CREATE TABLE line:

CREATE TABLE Orders (ID INT, Customer INT, Purchase CHAR(255), FOREIGN KEY (Customer) REFERENCES Customers(ID) ON DELETE CASCADE);

The "ON DELETE CASCADE" is the important part, as it instructs the DBMS to "cascade" deletes when the referenced row is deleted. That is, if a customer gets deleted, all their entries in Orders will be deleted also. This can be demonstrated with the same query as before:

mssql_query("DELETE FROM Customers WHERE ID = 1;");

This time Paul's entry in Customers is deleted without error, and all three of his rows in Orders are deleted also, thus leaving the database consistent.

There is one final use for referential integrity, however it is not supported in all DBMSs. Using the syntax ON DELETE SET NULL, the foreign key field will be set to NULL if possible if the referenced row is deleted. This has the effect of keeping the data intact, but saying that the row it referenced no longer exists, however it will not work if the referenced row has been declared NOT NULL. There are not many situations where SET NULL is recommended, and, thanks to it not being available on all DBMSs, it is generally best avoided.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.