Cascading Referential Integrity Constraints in SQL Server Management Studio
Last Updated : 06 Jan, 2023
In the Microsoft SQL server if we want to delete any record or column from one table but that record or column is a foreign key for another table then we will get the error to solve this problem we use Cascading referential integrity constraint.
It allows the actions that SQL Server should take when a user tries to delete or update a key to which an existing foreign key points. Suppose we have two tables, the first table's name is"Student" and the second is "Department" as follows,
Student and Department TableIn the Student table "Roll_no" is the primary key which identifies each record uniquely and in the Department table "ID" is the primary key. Here the foreign key is Dept_ID in the Student table get the reference from the primary key ID from the Department table.
In the Department table, if you delete the row with ID=1 then the records with Roll_no=1 and 2 from the Student table become an unsupported records which is also called as Orphan Record. Consequently, you won't be able to determine the Department of this row. Therefore, Cascading Referential Integrity Constraint (CRI) can be used to specify what SQL Server should do in this situation. By default, the DELETE or UPDATE statement is rolled back and we receive an error.
Query:
DELETE from Department WHERE ID =1
QueryOutput:
output
To handle this type of error by using Cascading Referential Integrity constraint following actions can be performed.
Here are the options when setting up Cascading referential integrity constraints:
- No Action: This is the default behaviour. An error is raised and the DELETE or UPDATE is rolled back if we attempt to delete or update a row whose key is referenced with existing rows in other tables.
- Cascade: Specifies that all rows containing those foreign keys are removed if we attempt to delete or update a row with a key that is referenced by existing rows in other tables.
- Set NULL: Specifies that all rows containing those foreign keys are set to NULL if we attempt to delete or update a row with a key that is referenced by existing rows in other tables.
- Set Default: Specifies that all rows containing those foreign keys are set to a default value if an attempt is made to delete or update a row with a key referenced by existing rows in other tables.
By using the following steps we can perform the above operations or actions on the tables:
Step 1: In SQL server management studio go to the Keys of Table and select Foreign key :
Step 2: After clicking "FK__student__Dept_ID__797309D9" go to the Foreign key Relationship:
Step 3: In Foreign key Relationship click on INSERT And UPDATE Specification, and you will get the options such as No Action, Cascade, Set NULL, and Set Default.
From the above steps, we can use the designer of SQL Server Management Studio to set Cascading Referential Integrity Constraints for all the above mention actions. Also by using query you can you can specify cascading referential integrity constraints when creating a foreign key in SQL Server:
ALTER TABLE student ADD FOREIGN KEY (Dept_ID) REFERENCES Department(ID) ON DELETE CASCADE ON UPDATE SET NULL;
Now if we set the Delete rule of Insert and update specification as cascade and perform the DELETE operation on the Gender table then,
delete from Department where ID=1
select * from Student select * from department
Output: After Successful Completion of the Delete Query, the tables will be:
Here you can see that the row with ID =1 from Department Table has been deleted and also the consecutive rows whose Dept_ID is 1 from the student table have also been Deleted. Finally Cascading Referential Integrity Constraints helps the user to delete or update the foreign key column data from one table and also change the consecutive table record also.
Similar Reads
SQL Query to Demonstrate Updation Anomaly in Referential Integrity in a Table
In SQL, there exists a concept of referential integrity. This means that a foreign key can take reference from the primary key of another table. There exist basically 3 anomalies in this concept. Here, we discuss about Updation Anomaly. This means that if an entry is present in the foreign key colum
3 min read
SQL Query to Demonstrate Addition Anomaly in Referential Integrity in a Table
In SQL, there exists a concept of referential integrity. This means that a foreign key can take reference from the primary key of another table. There exists basically 3 anomalies in this concept. Here, we discuss about Addition/Insertion Anomaly. This means that if an entry is absent in the primary
3 min read
Difference between Entity constraints, Referential constraints and Semantic constraints
Constraints are used in relational databases to follow the standards of the database and guarantee that the data is valid. Users are usually confused about various kinds of constraints most of which include entity constraints, referential constraints and semantic constraints. In this article, We wil
6 min read
Adding multiple constraints in a single table
Prerequisite - SQL Constraints We can create a table with more than one constraint in its columns. Following example shows how we can define different constraints on a table. Adding constraints in Create command : Sr_no is a Primary Key. Branch_no is the foreign key referencing Branch table. Company
2 min read
Check Constraint in MS SQL Server
Check Constraint : It is used alongside relational operators to check whether a value satisfies the condition or not (boolean). If the condition is satisfied, the boolean expression sets to True otherwise False. The check constraint does not have a specific syntax. It is used along with the create t
2 min read
SQL | Checking Existing Constraints on a Table using Data Dictionaries
Prerequisite: SQL-Constraints In SQL Server the data dictionary is a set of database tables used to store information about a database's definition. One can use these data dictionaries to check the constraints on an already existing table and to change them(if possible). USER_CONSTRAINTS Data Dictio
2 min read
Unique Constraint in MS SQL Server
A table might have repetitive data in form of rows. This might cause glitches while retrieving data from the query. To avoid them, a unique constraint is used. Unique allows sorting a column or set of columns uniquely meaning, a user cannot insert a duplicate or repeated value in a column as it resu
2 min read
How to Delete a Foreign Key Constraint in SQL
Foreign key constraints are important for maintaining referential integrity in a relational database as they define relationships between tables. However, there are times when we may need to identify which foreign key constraints reference a specific table in SQL Server. This could be necessary for
4 min read
How to Use SQL Query to Rename a Constraint?
In SQL, we sometimes need to rename the constraints of a table. The whole process for doing the same is demonstrated below. For this article, we will be using the Microsoft SQL Server as our database. Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.
2 min read
SQL Query to Display All the Existing Constraints on a Table
In SQL, we sometimes need to display all the currently existing constraints on a table. The whole process for doing the same is demonstrated below. For this article, we will be using the Microsoft SQL Server as our database. Step 1: Create a Database. For this use the below command to create a datab
3 min read