Referential Integrity in SQL Server

Referential Integrity 

Referential integrity states that a record referencing another record in another table must always refer to an existing record.

In other words, A record in table X can not point to a record in table  Y that does not exist.

Referential integrity is a constraint that always concerns two tables, and is enforced through foreign keys.

The main reason behind the use of referential integrity is that it maintains data consistency and data integrity.

What is Primary Key and Foreign Key?

Primary Key: 

• Main purpose of primary key is to uniquely identify records.
• Primary key need to be defined on column that does not accept duplicate or null value.
• Primary key Constraints are time-invariant, meaning that they must hold for the current data in the table and also for any future data.

Foreign Key:

       Foreign Key is designated column that point  to a primary key of another table.

      Table with the foreign key is called the child table, and table with the primary key is called referenced or parent table.

Now, we will understand referential integrity through an example.

- We have a table Customer  and table Product. We will establish referential integrity between this tables through foreign key.

Columns in table Customer are as below.

• Customer_ID 
• Name
• Gender
• Age
• Product_ID

Columns in Product table are as below.

• ID
• Product_Name
• Price

So, if we define a foreign key in the table "Customer" referencing the table "Product" then referential integrity will held from "Customer" to "Product".  For referential integrity we must create primary key in parent table and foreign key in child table. Customer table will be our child table and Product table will be our parent table. 

Referential integrity can be violated in two ways.

1. If we try to delete a record from Product table which is already referenced to Customer table.
2. If we try to insert a record in Customer table which does not exist in Product table, we have a violation.

We must have to define what actions are allowed if referential integrity is violated. There are four actions as given below.

1. NO ACTION
2. CASADE
3. SET NULL
4. SET DEFAULT

CODE:

CREATE TABLE Customer (Customer_ID PRIMARY KEY,

                       Name  varchar(64),

                       Gender VARCHAR(20),

                       Age int,

                       Product_ID int REFERENCES Product(ID) ON DELETE NO ACTION);

Here, we put " ON DELETE NO ACTION" which means if we delete data from Product table which is referenced to Customer table then system will throw an error.

If we put "ON DELETE CASCADE" , then first it will allow deletion in Product table and then it will automatically delete all referencing records in Customer table.

CODE:

CREATE TABLE Customer (Customer_ID PRIMARY KEY,

                       Name  varchar(64),

                       Gender VARCHAR(20),

                       Age int,

                       Product_ID int REFERENCES Product(ID) ON DELETE  CASCADE);

There is another two options called "SET NULL" and "SET DEFAULT".

If we delete record from Product table than SET NULL option will set referencing records in Customer table to the "NULL".  

CODE:

CREATE TABLE Customer (Customer_ID PRIMARY KEY,

                       Name  varchar(64),

                       Gender VARCHAR(20),

                       Age int,

                       Product_ID int REFERENCES Product(ID) ON DELETE  SET NULL);

 SET DEFAULT option will set the referencing records to the "DEFAULT" value. For SET DEFAULT option  first we need to define DEFAULT value.

CODE:

CREATE TABLE Customer (Customer_ID PRIMARY KEY,

                       Name  varchar(64),

                       Gender VARCHAR(20),

                       Age int,

                       Product_ID int REFERENCES Product(ID) ON DELETE  SET DEFAULT);

In this way with the help of foreign key we can define referential integrity to maintain data consistency.