REFERENTIAL_CONSTRAINTS

Transact-SQL Reference

Transact-SQL Reference

REFERENTIAL_CONSTRAINTS

Contains one row for each foreign constraint in the current database. This information schema view returns information about the objects to which the current user has permissions. The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view is based on the sysreferences, sysindexes, and sysobjects system tables.

To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name.

Column name Data type Description
CONSTRAINT_CATALOG nvarchar(128) Constraint qualifier.
CONSTRAINT_SCHEMA nvarchar(128) Constraint owner.
CONSTRAINT_NAME sysname Constraint name.
UNIQUE_CONSTRAINT_CATALOG nvarchar(128) Unique constraint qualifier.
UNIQUE_CONSTRAINT_SCHEMA nvarchar(128) Unique constraint owner.
UNIQUE_CONSTRAINT_NAME sysname Unique constraint.
MATCH_OPTION varchar(7) Referential constraint-matching conditions. Always returns NONE, which means that no match is defined. The condition is considered a match if
  • At least one value in the foreign key column is NULL;

    Or

  • All values in the foreign key column are not NULL and there is a row in the primary key table with exactly the same key.
UPDATE_RULE varchar(9) The action that is taken if a Transact-SQL statement violates referential integrity defined by this constraint.

Returns either NO ACTION or CASCADE. If NO ACTION is specified on ON UPDATE for this constraint, then the update of the primary key referenced in the constraint will not be propagated to the foreign key. If such update of a primary key will cause a referential integrity violation because at least one foreign key contains the same value, SQL Server will not execute any change to the parent and referring tables. SQL Server also will raise an error.

If CASCADE is specified on ON UPDATE for this constraint, then any change to the primary key value is automatically propagated to the foreign key value.

DELETE_RULE varchar(9) The action that is taken if a Transact-SQL statement violates referential integrity defined by this constraint.

Returns either NO ACTION or CASCADE. If NO ACTION is specified on ON DELETE for this constraint, then the delete on the primary key referenced in the constraint will not be propagated to the foreign key. If such delete of a primary key will cause a referential integrity violation because at least one foreign key contains the same value, SQL Server will not execute any change to the parent and referring tables. SQL Server also will raise an error.

If CASCADE is specified on ON DELETE on this constraint, then any change to the primary key value is automatically propagated to the foreign key value.

See Also

sysindexes

sysobjects

sysreferences