I have table Functions, with this table 3 more tables are joined with the foreign key. I want to delete total 50 rows in Functions table. But It shows cannot delete"The delete statement conflicted with reference". How to delete all values what have the same Id. Please help me anyone to get out this problem.,
When you delete records from a parent table, if the records has any foreign key references deletes\updates will fail to make the data consistent, so Delete rows from the child table first, then delete the rows from Parent table. e.g if you are deleting any products from product table, the corresponding reference records like (Product details, order details, etc) should be deleted first.
Another option is to change your [foreign key] constraint to perform [cascading deletes]. For example: --Get rid of the old foreign key constraint ALTER TABLE dbo.Child DROP CONSTRAINT FK_Child_Parent; --Add in a new foreign key constraint ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID) REFERENCES dbo.Parent(ID) ON DELETE CASCADE; Then, when you delete from dbo.Parent, it will automatically delete from dbo.Child as well. You would want to use this if there is no reason for a Child record to exist without a parent (for example, an order line item is meaningless without a corresponding order, so if you delete an order, you would want to delete the line items as well). The downside is that you lose the protection that you currently have: right now, you cannot lose a Child record or cause a Child record to fail referential integrity checks simply by deleting a row in the Parent table, because you get an error instead. Cascading deletes gets rid of the error; you just need to make sure that you don't accidentally delete more than you expected. :