How to Maintain Referential Integrity in a Multitable SQL Database
Even if every table in your SQL system has entity integrity and domain integrity, you may still have a problem because of inconsistencies in the way one table relates to another. In most well-designed multitable databases, every table contains at least one column that refers to a column in another table in the database.
These references are important for maintaining the overall integrity of the database. The same references, however, make update anomalies possible. Update anomalies are problems that can occur after you update the data in a row of a database table.
Trouble between parent and child tables
The relationships among tables are generally not bidirectional. One table is usually dependent on the other. Say that you have a database with a CLIENT table and an ORDERS table. You may enter a client into the CLIENT table before she makes orders. You can’t, however, enter an order into the ORDERS table unless you already have an entry in the CLIENT table for the client who’s making that order.
The ORDERS table is dependent on the CLIENT table. This kind of arrangement is often called a parent-child relationship, where CLIENT is the parent table and ORDERS is the child table. The child is dependent on the parent.
Generally, the primary key of the parent table is a column (or group of columns) that appears in the child table. Within the child table, that same column (or group) is a foreign key. Keep in mind, however, that a foreign key need not be unique.
Update anomalies arise in several ways between parent and child tables. A client moves away, for example, and you want to delete her information from your database. If she has already made some orders (which you recorded in the ORDERS table), deleting her from the CLIENT table could present a problem.
You’d have records in the ORDERS (child) table for which you have no corresponding records in the CLIENT (parent) table. Similar problems can arise if you add a record to a child table without making a corresponding addition to the parent table.
The corresponding foreign keys in all child tables must reflect any changes to the primary key of a row in a parent table; otherwise an update anomaly results.
Use cascading deletions with care
You can eliminate most referential integrity problems by carefully controlling the update process. In some cases, you have to cascade deletions from a parent table to its children. To cascade a deletion when you delete a row from a parent table, you also delete all the rows in its child tables whose foreign keys match the primary key of the deleted row in the parent table.
Take a look at the following example:
CREATE TABLE CLIENT ( ClientName CHAR (30) PRIMARY KEY, Address1 CHAR (30), Address2 CHAR (30), City CHAR (25) NOT NULL, State CHAR (2), PostalCode CHAR (10), Phone CHAR (13), Fax CHAR (13), ContactPerson CHAR (30) ) ; CREATE TABLE TESTS ( TestName CHAR (30) PRIMARY KEY, StandardCharge CHAR (30) ) ; CREATE TABLE EMPLOYEE ( EmployeeName CHAR (30) PRIMARY KEY, ADDRESS1 CHAR (30), Address2 CHAR (30), City CHAR (25), State CHAR (2), PostalCode CHAR (10), HomePhone CHAR (13), OfficeExtension CHAR (4), HireDate DATE, JobClassification CHAR (10), HourSalComm CHAR (1) ) ; CREATE TABLE ORDERS ( OrderNumber INTEGER PRIMARY KEY, ClientName CHAR (30), TestOrdered CHAR (30), Salesperson CHAR (30), OrderDate DATE, CONSTRAINT NameFK FOREIGN KEY (ClientName) REFERENCES CLIENT (ClientName) ON DELETE CASCADE, CONSTRAINT TestFK FOREIGN KEY (TestOrdered) REFERENCES TESTS (TestName) ON DELETE CASCADE, CONSTRAINT SalesFK FOREIGN KEY (Salesperson) REFERENCES EMPLOYEE (EmployeeName) ON DELETE CASCADE ) ;
The constraint NameFK names ClientName as a foreign key that references the ClientName column in the CLIENT table. If you delete a row in the CLIENT table, you also automatically delete all rows in the ORDERS table that have the same value in the ClientName column as those in the ClientName column of the CLIENT table.
The deletion cascades down from the CLIENT table to the ORDERS table. The same is true for the foreign keys in the ORDERS table that refer to the primary keys of the TESTS and EMPLOYEE tables.
Alternative ways to control update anomalies
You may not want to cascade a deletion. Instead, you may want to change the child table’s foreign key to a NULL value. Consider the following variant:
CREATE TABLE ORDERS ( OrderNumber INTEGER PRIMARY KEY, ClientName CHAR (30), TestOrdered CHAR (30), SalesPerson CHAR (30), OrderDate DATE, CONSTRAINT NameFK FOREIGN KEY (ClientName) REFERENCES CLIENT (ClientName), CONSTRAINT TestFK FOREIGN KEY (TestOrdered) REFERENCES TESTS (TestName), CONSTRAINT SalesFK FOREIGN KEY (Salesperson) REFERENCES EMPLOYEE (EmployeeName) ON DELETE SET NULL ) ;
The constraint SalesFK names the Salesperson column as a foreign key that references the EmployeeName column of the EMPLOYEE table. If a salesperson leaves the company, you delete her row in the EMPLOYEE table. New salespeople are eventually assigned to her accounts, but for now, deleting her name from the EMPLOYEE table causes all of her orders in the ORDER table to receive a null value in the Salesperson column.
You can also keep inconsistent data out of a database by using one of these methods:
Refuse to permit an addition to a child table until a corresponding row exists in its parent table. If you refuse to permit rows in a child table without a corresponding row in a parent table, you prevent the occurrence of “orphan” rows in the child table. This refusal helps maintain consistency across tables.
Refuse to permit changes to a table’s primary key. If you refuse to permit changes to a table’s primary key, you don’t need to worry about updating foreign keys in other tables that depend on that primary key.