Referential integrity in SQL Server

Referential integrity in SQL Server is a set of rules that ensures relationships between tables remain accurate and consistent. It is enforced through foreign key constraints, which link a column (or columns) in one table to the primary key in another table, preventing invalid or orphaned data.

Foreign Key Constraint:
    A foreign key in one table must reference a valid primary key in the related table.

Data Consistency:
    Ensures that any foreign key value in the child table corresponds to a primary key value in the parent table.

Actions to Maintain Integrity:
SQL Server provides options to handle updates or deletions in the parent table:
    ON DELETE CASCADE: Deletes related rows in the child table when a parent row is deleted.
    ON UPDATE CASCADE: Updates foreign key values in the child table when the primary key in the parent table changes.
    SET NULL: Sets the foreign key value to NULL when the parent record is deleted or updated.
    SET DEFAULT: Sets the foreign key value to a predefined default value.
    NO ACTION: Prevents changes to the parent row if related child rows exist.

Here is example script to understand this.

Without Referential Integrity

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name NVARCHAR(100)
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT, -- References Customers.CustomerID
  OrderDate DATE
);

In this case, there’s no guarantee that CustomerID in the Orders table matches a valid CustomerID in the Customers table.

With Referential Integrity:

CREATE TABLE Customers (
	CustomerID INT PRIMARY KEY,
	Name NVARCHAR(100)
);

CREATE TABLE Orders (
	OrderID INT PRIMARY KEY,
	CustomerID INT,
	OrderDate DATE,
	CONSTRAINT FK_Customer_Order FOREIGN KEY (CustomerID)
		REFERENCES Customers(CustomerID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

Referential integrity in SQL Server is a database concept that ensures relationships between tables remain consistent. It enforces rules that maintain the validity and accuracy of data across related tables, primarily through the use of foreign keys.

Key Points:

1. Foreign Keys: A foreign key in a child table points to a primary key in a parent table. This establishes a relationship between the two tables.

2. Consistency: Referential integrity ensures that:

   • A foreign key value must match an existing primary key value in the parent table.
   • If a primary key value is deleted or updated, any corresponding foreign key values in the child table are also affected (either restricted, set to null, or cascaded).

3. Actions: SQL Server allows specific actions when referential integrity is violated:

   • CASCADE: Automatically updates or deletes related rows in child tables.
   • SET NULL: Sets foreign key values to null when the corresponding primary key is deleted.
   • NO ACTION/RESTRICT: Prevents deletion or updating of a primary key if related foreign key values exist.