Delete data with foreign key in SQL Server table

Question

I m going to delete data in an SQL Server table  parent  which has a relationship with another table  child   I tried the basic Delete query  But it isn t working  and I know it won t    DELETE FROM table WHERE       It returned following error     The DELETE statement conflicted with the REFERENCE constraint       I need to keep the table s schema  I know that I just need to add some words in the query  I ve ever done this before  but I just couldn t recall it

User · Answer

To delete data from the tables having relationship of parent child  First you have to delete the data from the child table by mentioning join then simply delete the data from the parent table  example is given below   DELETE ChildTable FROM ChildTable inner join ChildTable on PParentTable ID ChildTable ParentTableID WHERE  lt WHERE CONDITION gt     DELETE  ParentTable WHERE  lt WHERE CONDITION gt

User · Answer

SET foreign key checks   0  DELETE FROM yourtable  SET foreign key checks   1

User · Answer

You need to manually delete the children   the  lt condition gt  is the same for both queries   DELETE FROM child FROM cTable AS child INNER JOIN table AS parent ON child ParentId   parent ParentId WHERE  lt condition gt    DELETE FROM parent FROM table AS parent WHERE  lt condition gt

User · Answer

You can disable and re-enable the foreign key constraints before and after deleting   alter table MyOtherTable nocheck constraint all delete from MyTable alter table MyOtherTable check constraint all

User · Answer

Set the FOREIGN KEY CHECKS before and after your delete SQL statements   SET FOREIGN KEY CHECKS   0  DELETE FROM table WHERE     DELETE FROM table WHERE     DELETE FROM table WHERE     SET FOREIGN KEY CHECKS   1    Source  https   alvinalexander com blog post mysql drop-mysql-tables-in-any-order-foreign-keys

User · Answer

here you are adding the foreign key for your  Child  table   ALTER TABLE child ADD FOREIGN KEY  P Id  REFERENCES parent P Id   ON DELETE CASCADE ON UPDATE CASCADE       After that if you make a DELETE query on  Parent  table like this   DELETE FROM parent WHERE            since the child has a reference to parent with DELETE CASCADE  the  Child  rows also will be deleted  along with the  parent

User · Answer

If you wish the delete to be automatic  you need to change your schema so that the foreign key constraint is ON DELETE CASCADE   For more information  see the MSDN page on Cascading Referential Integrity Constraints   ETA  after clarification from the poster   If you can t update the schema  you have to manually DELETE the affected child records first

User · Answer

Usefull script which you can delete all data in all tables of a database   replace tt with you databse name    declare  tablename nvarchar 100  declare c1 cursor for SELECT TABLE NAME FROM INFORMATION SCHEMA TABLES WHERE TABLE CATALOG  tt  AND TABLE TYPE  BASE TABLE   open  c1 fetch next from c1 into  tablename  while   FETCH STATUS   0     begin     print  t1         exec  alter table      tablename     nocheck constraint all           exec  delete from      tablename          exec   alter table      tablename     check constraint all           fetch next from c1 into  tablename     end close c1 DEALLOCATE c1

User · Answer

So  you need to DELETE related rows from conflicted tables or more logical to UPDATE their FOREIGN KEY column to reference other PRIMARY KEY s from the parent table   Also  you may want to read this article Don   t Delete     Just Don   t

[sql-server] Delete data with foreign key in SQL Server table

Examples related to sql-server