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 referential integrity constraints in these examples are defined at the column level, using the references keyword in the create table statement.
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))
Any store specified in the store_employees table must be included in the stores table. The references constraint enforces this by verifying 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 verifying that any value inserted into the mgr_id column must already exist in the emp_id column.