The DELETE statement conflicted

Shweta68

7y6.2k

Hi,

I write a simple delete query. And execute that query. Then I found below error.

Msg 547, Level 16, State 0, Line 5
The DELETE statement conflicted with the REFERENCE constraint "FK_tb_state_tb_country". The conflict occurred in database "EmployeeDB", table "dbo.tb_state", column 'country_id'.
The statement has been terminated.

Is there any solution for above??

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Code Alone7y ago
Accepted answer
Hi,
You can delete data from both tables by writing query on master table only. To perform this you need to use DELETE CASCADE option in table creation/alter. You must execute below query first .
ALTER TABLE tb_state WITH CHECK
ADD CONSTRAINT FK_tb_state_tb_countr FOREIGN KEY(country_id)
REFERENCES tb_country (country_id)
ON DELETE CASCADE
After this alter, use your delete query on master table. This will work for sure.
*Note : Using cascade have many side effects. You can read them from https://dba.stackexchange.com/questions/153351/delete-statement-conflicted-with-the-reference-constraint
+2
Shweta687y ago
Thank you Jignesh Sir and Pradeep Sir. Both answers were helpful.
Thank you both
0
Jignesh Kumar7y ago
Hi Swetha,

Please find below link and look at last answer which will help to resolve your query. You can use cascadedelete.

https://forums.asp.net/t/1557503.aspx?Help+The+DELETE+statement+conflicted+with+the+REFERENCE+constraint
+1
Shweta687y ago
Thank you Jignesh Sir.
I am using reference. But I want to delete the records from both table without writing the delete query on 2 tables.
0
Jignesh Kumar7y ago
Hi,You have reference key for state table from country. So if you are delete from primary key table then you have to delete first from refrence key table. First delete from reference table and then parent table. Please have a lool at pk or fk refernce of both table. Still issue exists than share your both table structure and query which will help member to resolve it. https://www.tsql.info/error/msg-547-level-16-the-delete-statement-conflicted-with-the-reference-constraint.php
0