Entity integrity means that in each table the primary key (which may be composite) satisfies both of two conditions: (1) that the primary key is unique within the table and (2) that the primary key column(s) contains no null values.
I prefer to think of referential integrity as a property that applies (or fails to apply) to a database as a whole. In this sense, referential integrity means that in the database as a whole, things are set up in such a way that if a column exists in two or more tables in the database (typically as a primary key in one table and as a foreign key in one or more other tables), then any change to a value in that column in any one table will be reflected in corresponding changes to that value where it occurs in other tables. This means that the RDBMS must be set up so as to take appropriate actions to spread a change—in one table—from that table to the other tables where the change must also occur.
The effect of the existence and maintenance of referential integrity is, in short, that if a column exists in two or more tables in the database, every occurrence of the column will contain only values that are consistent across the database.
For example, suppose that in a company's database a column SALESREP_ID exists in the SALESREP table, in the ACCOUNTS table, and in the CUSTOMER table. Then that column in the CUSTOMER table will contain only values of SALESREP_ID that also occur in the SALESREP table. To put it another way, a customer may or may not have an assigned sales representive, but if the customer does have a sales representative assigned to him or her, then that sales representative's ID in the CUSTOMER table must be an ID that exists in the SALESREP table. Similarly, in the ACCOUNTS table, every value of SALESREP_ID that does occur must be a value that occurs in the SALESREP table. Not every account has to have a sales representative currently assigned to it, but if an account does have one or more sales representatives assigned to it, then every such sales representative must also exist in the SALESREP table.
SQL clauses that are often involved in maintaining
referential integrity include the DELETE RESTRICT, DELETE CASCADE, and UPDATE
CASCADE clauses.
Last revised 2004 Feb 23