Constraints and Referential Integrity in SQL Server

Constraints

Constraint specifications add additional restrictions on the contents of the table. They are automatically enforced by the DBMS. The columnconstraints are:

  • NOT NULL — specifies that the column can’t be set to null. If this constraint is not specified, the column is nullable, that is, it can be set to null. Normally, primary key columns are declared as NOT NULL.
  • PRIMARY KEY — specifies that this column is the only column in the primary key. There can be only one primary key declaration in a CREATE TABLE.
  • UNIQUE — specifies that this column has a unique value or nullfor all rows of the table.
  • REFERENCES — specifies that this column is the only column in a foreign key. For foreign keys with multiple columns, use the FOREIGN KEY tableconstraint. See Referential Integritybelow for a detailed description of primary keys.
  • CHECK — specifies a user defined constraint on the table.

The tableconstraints are:

  • PRIMARY KEY — specifies the set of columns that comprise the primary key. There can be only one primary key declaration in a CREATE TABLE Statement.
  • UNIQUE — specifies that a set of columns have unique values (or nulls) for all rows in the table. The UNIQUE specifier is followed by a parenthesized list of column names, separated by commas.
  • FOREIGN KEY — specifies the set of columns in a foreign key. See Referential Integrity below for a detailed description of foreign keys.
  • CHECK — specifies a user defined constraint, known as a check condition. The CHECK specifier is followed by a predicate enclosed in parentheses. For Intermediate Level SQL92, the CHECK predicate can only reference columns from the current table row, with no subqueries. Many DBMSs support subqueries in the check predicate.

Referential Integrity
Foreign keys provide relationships between tables in the database. In relational, a foreign key in a table is a set of columns that reference the primary key of another table. For each row in the referencing table, the foreign key must match an existing primary key in the referenced table. The enforcement of this constraint is known as Referential Integrity.

Referential Integrity requires that:

The columns of a foreign key must match in number and type the columns of the primary key in the referenced table.
The values of the foreign key columns in each row of the referencing table must match the values of the corresponding primary key columns for a row in the referenced table.

The one exception to the second restriction is when the foreign key columns for a row contain nulls. Since primary keys should not contain nulls, a foreign key with nulls cannot match any row in the referenced table. However, a row with a foreign key where any foreign key column contains null is allowed in the referencing table. No corresponding primary key value in the referenced table is required when any one (or more) of the foreign key columns is null. Other columns in the foreign key may be null or non-null. Such a foreign key is a null reference, because it does not reference any row in the referenced table.

Like other constraints, the referential integrity constraint restricts the contents of the referencing table, but it also may in effect restrict the contents of the referenced table. When a row in a table is referenced (through its primary key) by a foreign key in a row in another table, operations that affect its primary key columns have side-effects and may restrict the operation. Changing the primary key of or deleting a row which has referencing foreign keys would violate the referential integrity constraints on the referencing table if allowed to proceed. This is handled in two ways,

The referenced table is restricted from making the change (and violating referential integrity in the referencing table), or
Rows in the referencing table are modified so the referential integrity constraint is maintained.

These actions are controlled by the referential integrity effects declarations, called referential triggers by SQL92. The referential integrity effect actions defined for SQL are:

NO ACTION — the change to the referenced (primary key) table is not performed. This is the default.
CASCADE — the change to the referenced table is propagated to the referencing (foreign key) table.
SET NULL — the foreign key columns in the referencing table are set to null.

Update and delete have separate action declarations. For CASCADE, update and delete also operate differently:

For update (the primary key column values have been modified), the corresponding foreign key columns for referencing rows are set to the new values.
For delete (the primary key row is deleted), the referencing rows are deleted.

A referential integrity constraint in the CREATE STATEMENT has two forms. When the foreign key consists of a single column, it can be declared as a column constraint, like:

column-descr REFERENCES references-specification

As a table constraint, it has the following format:

FOREIGN KEY (column-list) REFERENCES references-specification

column-list is the referencing table columns that comprise the foreign key. Commas separate column names in the list. Their order must match the explicit or implicit column list in the references-specification.

The references-specification has the following format:

table-2 [ ( referenced-columns ) ]
[ ON UPDATE { CASCADE | SET NULL | NO ACTION }]
[ ON DELETE { CASCADE | SET NULL | NO ACTION }]

The order of the ON UPDATE and ON DELETE clauses may be reversed. These clauses declare the effect action when the referenced primary key is updated or deleted. The default for ON UPDATE and ON DELETE is NO ACTION.

table-2 is the referenced table name (primary key table). The optional referenced-columns list the columns of the referenced primary key. Commas separate column names in the list. The default is the primary key list in declaration order.

Contrary to the relational model, SQL92 allows foreign keys to reference any set of columns declared with the UNIQUE constraint in the referenced table (even when the table has a primary key). In this case, the referenced-columns list is required.

Example table constraint for referential integrity (for the sp table):

FOREIGN KEY (sno)
REFERENCES s(sno)
ON DELETE NO ACTION
ON UPDATE CASCADE

extradrmtech

Since 30 years I work on Database Architecture and data migration protocols. I am also a consultant in Web content management solutions and medias protecting solutions. I am experienced web-developer with over 10 years developing PHP/MySQL, C#, VB.Net applications ranging from simple web sites to extensive web-based business applications. Besides my work, I like to work freelance only on some wordpress projects because it is relaxing and delightful CMS for me. When not working, I like to dance salsa and swing and to have fun with my little family.

You may also like...