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.
Loading
Benjamin KemnerPosted Jul 20, 2011, 5:25 AM
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;
Sahil JaniPosted Jul 20, 2011, 5:37 AM
And thanks to you Zoran also... for replying.
Zoran HorvatPosted Jul 20, 2011, 5:22 AM
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
GO
I don't have MSSQL instance here to check the syntax, but this should work.
Zoran