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
|
| 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. |