Referential integrity refers to the methods used to manage the relationships between tables. When you create a table, you can define constraints to ensure that the data inserted into a particular column has matching values in another table.
There are three types of references you can define in a table: references to another table, references from another table, and self-references, that is, references within the same table.
The following two tables from the pubs3 database illustrate how declarative referential integrity works. The first table, stores, is a “referenced” table:
create table stores (stor_id char(4) not null, stor_name varchar(40) null, stor_address varchar(40) null, city varchar(20) null, state char(2) null, country varchar(12) null, postalcode char(10) null, payterms varchar(12) null, unique nonclustered (stor_id))
The second table, store_employees, is a “referencing table” because it contains a reference to the stores table. It also contains a self-reference:
create table store_employees (stor_id char(4) null references stores(stor_id), emp_id id not null, mgr_id id null references store_employees(emp_id), emp_lname varchar(40) not null, emp_fname varchar(20) not null, phone char(12) null, address varchar(40) null, city varchar(20) null, state char(2) null, country varchar(12) null, postalcode varchar(10) null, unique nonclustered (emp_id))
The references defined in the store_employees table enforce the following restrictions:
Any store specified in the store_employees table must be included in the stores table. The references constraint enforces this by saying that any value inserted into the stor_id column in store_employees must already exist in the stor_id column in my_stores.
All managers must have employee identification numbers. The references constraint enforces this by saying that any value inserted into the mgr_id column must already exist in the emp_id column.
