Declarative Referential Integrity
||It has been suggested that this article be merged into Referential integrity. (Discuss) Proposed since October 2016.|
Declarative Referential Integrity (DRI) is one of the techniques in the SQL database programming language to ensure data integrity.
Meaning in SQL
A table (called the referencing table) can refer to a column (or a group of columns) in another table (the referenced table) by using a foreign key. The referenced column(s) in the referenced table must be under a unique constraint, such as a primary key. Also, self-references are possible (not fully implemented in MS SQL Server though). On inserting a new row into the referencing table, the relational database management system (RDBMS) checks if the entered key value exists in the referenced table. If not, no insert is possible. It is also possible to specify DRI actions on UPDATE and DELETE, such as CASCADE (forwards a change/delete in the referenced table to the referencing tables), NO ACTION (if the specific row is referenced, changing the key is not allowed) or SET NULL / SET DEFAULT (a changed/deleted key in the referenced table results in setting the referencing values to NULL or to the DEFAULT value if one is specified).
ANSI/ISO/IEC 9075-1:2003, Information technology—Database languages—SQL—Part 1: Framework (SQL/Framework) ANSI/ISO/IEC 9075-2:2003, Information technology—Database languages—SQL—Part 2: Foundation (SQL/Foundation)
Product specific meaning
In Microsoft SQL Server the term DRI also applies to the assigning of permissions to users on a database object. Giving DRI permission to a database user allows them to add foreign key constraints on a table.
- Microsoft Support (2007-02-11). "Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths". microsoft.com. Retrieved 2009-01-24.
- Chigrik, Alexander (2003-08-13). "Managing Users Permissions on SQL Server". Database Journal. Retrieved 2006-12-17.