Inserting a Primary Key

Verification required: None. The RI rule specifies that all foreign keys must reference an existing primary key. When a new PK is inserted for the first time, it will not have any foreign keys to worry about, so RI is maintained. The definition of a primary key stipulates, however, that it must be unique, so to satisfy this rule, you must ensure that another key like the one being inserted does not exist. (This can be supported by the server with a unique index.)

Updating a Primary Key

Verification required: All foreign keys that reference the old primary key must be changed. You have several options: You can change the FKs to the new value (cascade the change), delete the FKs, or change them to a completely different, existing PK value.

Example: If a publisher in the publishers table changed its ID, it would be necessary to change all the rows in the titles table that referenced the old pub_id to the new pub_id. There are also foreign keys in the employee and pub_info tables, so those would also need to change.

Deleting a Primary Key

Verification required: The primary key may not have any existing foreign keys.

Example: To remove a publisher from the pubs database, first delete all the titles by this publisher in the titles table. Then, delete all the employees who worked for this publisher in the employee table, and the information about this publisher in pub_info. This removes all foreign key references. After this step is complete, it is then possible to safely remove the row from publishers.

You could use a trigger to delete any matching foreign keys every time a primary key was removed. This is called a cascading delete.

Inserting a Foreign Key

Verification required: The foreign key value must exist in the primary key table.

Example: In order to add a title, you would need to know who wrote and who published it. Inserting a title into the titles table requires an existing au_id and an existing pub_id.

Updating a Foreign Key

Verification Required: The new key value must exist in the primary key table. The old key value is irrelevant.

Example: A book’s rights were sold from one publisher to another. If both publishers are already defined in the publishers table, you need only update the title to the new publisher’s pub_id. (If the new publisher didn’t exist, you would need to first add it and then update the titles table.)

Deleting a Foreign Key

Verification Required: None.

Example: You could safely delete the information about a publisher in pub_info without upsetting RI.

Implementing Declarative Referential Integrity

DRI uses restriction to implement referential integrity. DRI will prevent any operations that would violate referential integrity. If you want to cascade deletes of primary keys and updates of foreign keys, you must use triggers.

DRI is implemented by defining the PK and FK relationships between tables, usually when they are created. These defined relationships are called constraints.

A constraint that defines a primary key is called a primary key constraint and enforces uniqueness in that column or columns. Primary key columns cannot contain null values and cannot be defined as not null.

A constraint that defines a foreign key is called a reference constraint and enforces the RI rule on a column or columns. Reference constraints almost always reference defined primary key constraints, although they may also be defined on columns that have unique indexes or unique constraints. FK columns may be null without restrictions.

A unique constraint enforces uniqueness on a column or columns. They are helpful when a table has a PK defined, but has other columns or combinations of columns that should contain unique values. Unique constraint columns allow null values, but only once. In other words, only one row may be null for this column or columns.

The constraint definitions become a part of that table and disappear when the table disappears (that is, if it is ever dropped). Here is how DRI might look on the authors table, containing a primary key, the titleauthor table (with two foreign keys), and the titles table, with a PK and one FK.

---

Warning:  Don’t try running this SQL example! If you try to run this in the pubs database, you’ll get errors that tell you the tables already exist. Also, the tables require two user-defined types for which I didn’t include creation code. I’ve included these examples to give you an idea of what DRI looks like in real T-SQL code.

---

 /*
    Authors table: PK on au_id, defined at column level
 */
 CREATE TABLE authors(
     au_id id NOT NULL primary key,
     au_lname varchar (40) NOT NULL ,
     au_fname varchar (20) NOT NULL ,
     phone char (12) NOT NULL,
     address varchar (40) NULL ,
     city varchar (20) NULL ,
     state char (2) NULL ,
     zip char (5) NULL ,
     contract bit NOT NULL ,
 )
 GO
 /*
    Titles table: PK on title_id, FK on pub_id defined at table level
 */
 CREATE TABLE dbo.titles (
     title_id tid NOT NULL,
     title varchar (80) NOT NULL,
     type char (12) NOT NULL,
     pub_id char (4) NULL,
     price money NULL,
     advance money NULL,
     royalty int NULL,
     ytd_sales int NULL,
     notes varchar (200) NULL,
     pubdate datetime NOT NULL,
     CONSTRAINT PK_CL_titleidind PRIMARY KEY(title_id),
     CONSTRAINT FK_titles_pub_id FOREIGN KEY(pub_id)
         REFERENCES publishers(pub_id)
 )
 go
 /*
    titleauthor table: references authors.au_id and
    titles.title_id, defined at column level
 */
 CREATE TABLE dbo.titleauthor (
     au_id id NOT NULL references authors(au_id),
     title_id tid NOT NULL references titles(title_id),
     au_ord tinyint NULL,
     royaltyper int NULL,
 )
 GO

This listing demonstrates how to create primary key and foreign key (reference) constraints, which make use of declarative referential integrity.

Order Matters

The first thing to note is that the order in which these tables are defined is important. The titleauthor table must come last, so that the columns in the titles and authors tables that it references exist. If they did not, you would receive an error. Also, it is necessary to create each table before continuing, which is accomplished by including GO in the script.