Solve Error Message 547 Level 16 in SQL Server

SQL Server throws the error msg 547 when a statement conflicts with the FOREIGN KEY/REFERENCE constraints. In others words SQL Server throws this error msg when we try to execute any SQL statement that conflicts with the FOREIGN KEY/REFERENCE constraints. This error is encountered when the primary key of a table is updated but it is referenced by a foreign key from another table and the update or delete specific is set to No action. The No action is the default option.

I think each developer and programmer encounters this error many times and this error can become a huge problem for a person that does not have sufficient  knowledge of SQL. Today I will explain a method to resolve this problem.

First of all we create two tables. First we create a Department table as in the following:

CREATE TABLE DEPARTMENT
(
Dept_Iidint  NOT null,
Dept_Name  [varchar](MAX) NOT NULL,
constraint Primary_Key PRIMARY KEY(Dept_Iid)
)

Now we create an Employee_Detail Table as in the following:

CREATE TABLE Employee_Detail
(
Emp_Iid  int  NOT  NULL,
Emp_Name  [varchar](MAX)  NOT  NULL,
Emp_Salary  int  NOT  NULL,
Dept_Iid  int  NOT  NULL,
CONSTRAINT  Primary_Key2  PRIMARY  KEY(Emp_Iid))

Then we create a Foreign Key in the Employee_Detail table that has a reference in the Department table as in the following:

ALTER TABLE  Employee_Detail
ADD  CONSTRAINT  FK_Employee_Details_Department  FOREIGN KEY(Dept_Iid)  REFERENCES  DEPARTMENT(Dept_Iid)

Now we Insert some data into both tables.

Insert data into the Department Table.

INSERT  INTO  DEPARTMENT
SELECT 1, 'HR'  UNION ALL
SELECT 2, 'CS'

Insert data into the Employee_Detail Table:

INSERT  INTO  Employee_Detail
SELECT 1,'Pankaj Choudhary',20,1 UNION  ALL
SELECT 2,'Rahul Prajapat',21,2 UNION  ALL
SELECT 3,'Sandeep Jangid',23,2 UNION  ALL
SELECT 4,'Sanjeev Baldia',22,1 UNION  ALL
SELECT 5,'Narendra Sharma',20,1

Let us try to delete some data from the Department table.

DELETE  FROM  DEPARTMENT  WHERE  Dept_Iid=1;

Output



Now we try to update some data into the Department table.

UPDATE  DEPARTMENT  SET  Dept_Iid=3 WHERE  Dept_Name='CS';

Output



As we expect, if we try to execute both queries then SQL Server will throw an error message. This error message shows that our SQL statement conflicts with the Reference Constraints.
Now we will see some methods to resolve this problem.

Method 1: Change the Delete and Update Rule for Foreign Key

When we create a foreign key on a table and don't define a rule for the Delete and Update statements then SQL Server by default sets the “No Action” rule for both commands. The No Action rule specifes that If a value is deleted or updated from the parent table then no action (change) will be done on the child table.

SQL Server provides the following 4 rules for Delete and Update statements.

 

• No Action: If a value is deleted or updated from a parent table then no action (change) will be done on the child table.
  
  
• Set NULL: Associated values in a child table would be set to NULL If any value is deleted or updated from a parent table.
  
  
• Cascade: If the value is updated in the parent table then the associated values in the child table would also be updated and if the value is deleted from the parent table then the associated values in the child table would also be deleted.
  
  
• Set Default: Associated values in a child table would be set to the default value specified in the column definition. Also, the default value should be present in the primary key column. Otherwise the basic requirement of a FK relation would fail and the update/delete operation would not be successful. If no default value is provided in the foreign key column then this rule could not be implemented.
  
  For resolving our problem we set the cascade rule for both Update and Delete statements.
  We have 2 method to do this same task.

Method 1

In this method first we delete the constraint that already exists and then we create a new constraint with a cascade rule to update and insert a command.

First drop the constraint that already exists.

ALTER  TABLE  Employee_Detail
DROP  CONSTRAINT  FK_Employee_Details_Department

Now we create a new constraint:

ALTER  TABLE  Employee_Detail
ADD  CONSTRAINT  FK_Employee_Details_Department  FOREIGN  KEY(Dept_Iid)  REFERENCES  DEPARTMENT(Dept_Iid)
ON  DELETE  CASCADE
ON  UPDATE  CASCADE

Now we try to delete and update some data.

DELETE  FROM  DEPARTMENT  WHERE  Dept_Iid=1;
UPDATE  DEPARTMENT  SET  Dept_Iid=5 WHERE  Dept_Name='CS';

Let us check the data of both tables:

SELECT  *  FROM  DEPARTMENT
SELECT  *  FROM  Employee_Detail

Output



As we expect, data is deleted and updated without any error. We can do this same task without any command.

Method 2

In this method first we right-click on the table that contains a foreign key and select the Design Option.



Now we right-click on a column and select the Relationship option.



Then we select a Foreign Key constraint and change the Delete and Update rule from No Action to Cascade.



Method 2: With Check Command

In this method first we disable the Foreign Key constraint then we implement our Delete or Update command and after completing the query we again enable the constraint.

Let us see an example.

/* Disable FK Constraint */
ALTER  TABLE  Employee_Detail  NOCHECK  CONSTRAINT  FK_Employee_Details_Department

/* Perform DELETE*/
DELETE  FROM  DEPARTMENT  WHERE  Dept_Iid=1;
DELETE  FROM  Employee_Detail  WHERE  Dept_Iid=1;

/* Perform UPDATE*/
UPDATE  DEPARTMENT  SET  Dept_Iid=5 WHERE  Dept_Name='CS';
UPDATE  Employee_Detail  SET  Dept_Iid=5 WHERE  Dept_IID=2 ;

/*Enable FK Constraint */
ALTER  TABLE  Employee_Detail  WITH  CHECK  CHECK  CONSTRAINT  FK_Employee_Details_Department

Now we select the data from both tables.

SELECT  *  FROM  DEPARTMENT
SELECT  *  FROM  Employee_Detail

Output



Method 3: Use CEHCK Command

This method is the same as Method 2 but instead of “WITH CHECK CHECK” we use the “CHECK” command to enable the constraint.

Let us see an example.

/* Disable FK Constraint */
ALTER  TABLE  Employee_Detail  NOCHECK  CONSTRAINT FK_Employee_Details_Department

/* Perform DELETE*/
DELETE  FROM  DEPARTMENT  WHERE  Dept_Iid=1;

/* Perform UPDATE*/
UPDATE  DEPARTMENT  SET  Dept_Iid=5 WHERE  Dept_Name='CS';

/*Enable FK Constraint */
ALTER  TABLE  Employee_Detail  CHECK  CONSTRAINT  FK_Employee_Details_DEPARTMENT

Output

Now we check the output.

SELECT  *  FROM  DEPARTMENT
SELECT  *  FROM  Employee_Detail

Output



Difference between Method 2 and Method 3

It is very important to understand the difference between method 2 and method 3. Now I explain the difference between both methods.

In Method 2 we use the “WITH CHECK CHECK” command to enable the foreign key constraint. This command first checks that a relationship hierarchy is present between both tables. If a relationship hierarchy is present between both tables then it will enable the constraint otherwise it will throw an error.

If we are using the “WITH CHECK CHECK” command then SQL Server first checks that the data is present in the child table. If a relational hierarchy is maintained between both tables (parent and child) then SQL Server enables the constraint otherwise it throws an error.

So we delete and update the data from both tables.

/* Perform DELETE*/
DELETE  FROM  DEPARTMENT  WHERE  Dept_Iid=1;
DELETE  FROM  Employee_Detail  WHERE  Dept_Iid=1;

/* Perform UPDATE*/
UPDATE  DEPARTMENT  SET  Dept_Iid=5 WHERE  Dept_Name='CS';
UPDATE  Employee_Detail  SET  Dept_Iid=5 WHERE  Dept_IID=2 ;

If we delete or update the data only from the DEPARTMENT but not from the Employee_Detail table then SQL Server will throw an error.

But in the case of the “CHECK” command SQL Server doesn't check the relationship hierarchy between both tables (parent and child). So the “CHECK” command was only enabled but it didn't validate the existing records when the constraint is enabled. But this command has a very big incorrect effect, that we lost the relationship hierarchy enable both tables (parent and child).