Referential Integrity Constraints

Referential integrity constraints require that data inserted into a referencing table that defines the constraint must have matching values in a referenced table.

A referential integrity constraint is satisfied for either of the following conditions:
- The data in the constrained columns of the referencing table contains a null value.
- The data in the constrained columns of the referencing table matches data values in the corresponding columns of the referenced table.
Using the pubs2 database as an example, a row inserted into the salesdetail table (which records the sale of books) must have a valid title_id in the titles table. salesdetail is the referencing table and titles table is the referenced table. Currently, pubs2 enforces this referential integrity using a trigger. However, the salesdetail table could include this column definition and referential integrity constraint to accomplish the same task:
```
title_id tid
    references titles (title_id)
```
The maximum number of table references allowed for a query is 192. Use sp_helpconstraint to check a table’s referential constraints.
A table can include a referential integrity constraint on itself. For example, the store_employees table in pubs3, which lists employees and their managers, has the following self-reference between the emp_id and mgr_id columns:
```
emp_id id primary key,
mgr_id id null
        references store_employees (emp_id),
```
This constraint ensures that all managers are also employees, and that all employees have been assigned a valid manager.
You cannot drop a referenced table until the referencing table is dropped or the referential integrity constraint is removed (unless it includes only a referential integrity constraint on itself).
The SAP ASE server does not enforce referential integrity constraints for temporary tables.
To create a table that references another user’s table, you must have references permission on the referenced table. For information about assigning references permissions, see the grant command.
Table-level, referential integrity constraints appear in the create table statement as separate items. They must include the foreign key clause and a list of one or more column names.

Column names in the references clause are optional only if the columns in the referenced table are designated as a primary key through a primary key constraint.

The referenced columns must be constrained by a unique index in that referenced table. You can create that unique index using either the unique constraint or the create index statement.
The datatypes of the referencing table columns must match the datatypes of the referenced table columns. For example, the datatype of col1 in the referencing table (test_type) matches the datatype of pub_id in the referenced table (publishers):
```
create table test_type
 (col1 char (4) not null
    references publishers (pub_id),
col2 varchar (20) not null)
```
The referenced table must exist when you define the referential integrity constraint. For tables that cross-reference one another, use the create schema statement to define both tables simultaneously. As an alternative, create one table without the constraint and add it later using alter table. See create schema or alter table for more information.
The create table referential integrity constraints offer a simple way to enforce data integrity. Unlike triggers, constraints cannot:
- Cascade changes through related tables in the database
- Enforce complex restrictions by referencing other columns or database objects
- Perform “what-if” analysis
Referential integrity constraints do not roll back transactions when a data modification violates the constraint. Triggers allow you to choose whether to roll back or continue the transaction depending on how you handle referential integrity.

Note: The SAP ASE server checks referential integrity constraints before it checks any triggers, so a data modification statement that violates the constraint does not also fire the trigger.