The DELETE statement conflicted

Question

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??

Code Alone · 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

Jignesh Kumar · Answer

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

Shweta68 · Answer

Thank you Jignesh Sir and Pradeep Sir. Both answers were helpful. Thank you both

Shweta68 · Answer

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.

Jignesh Kumar · Answer

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