Query Error

Question

Hi,

I am getting this error when i am deleting data from Request table. In request table RequestId is PK and in RequestData table RequestId is FK.

ERROR :::
The conflict occurred in database "XYZ", table "dbo.RequestData", column 'RequestId'

So, How will I write query that when I will delete RequestId from Request Table then automatically all the data will be deleted from the RequestData table for that particular requestId.

Benjamin Kemner · Answer

You should let the database do this job. If you create a foreign key on the database you can set the option "ON DELETE CASCADE" instead of "ON DELETE RESTRICT" (default). Try something like this: ALTER TABLE requestData ADD CONSTRAINT fk_requestData_request FOREIGN KEY (requestid) REFERENCES request (requestid) ON DELETE CASCADE;

Zoran Horvat · Answer

Sahil, Direct solution is to change the foreign key constraint to perform cascade delete. In that case RequestData rows with specified RequestId will be automatically deleted together with the Request row with that RequestId. If you already have foreign key constraint then you can drop it and re-create it with cascade delete clause. In MSSQL that would be: ALTER TABLE RequestData DROP CONSTRAINT ; GO ALTER TABLE RequestData ADD CONSTRAINT FOREIGN KEY (RequestId) REFERENCES Request (RequestId) ON DELETE CASCADE; GO I don't have MSSQL instance here to check the syntax, but this should work. Zoran

Sahil Jani · Answer

Thanks Benjamin... And thanks to you Zoran also... for replying.