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.
