CODEWITHSUNDEEP
sqlsql-server-2008
raklos
raklos

Reputation: 28545

Delete row when a table has an FK relationship

Hi I have 2 tables Document and Project.

DocumentID is an FK in the Project table.

Using sql How can I delete Document Records in the Document table, and also remove their corresponding records in the Project table.

Thanks

Upvotes: 1

Views: 8764

Answers (2)

EJC
EJC

Reputation: 2141

delete 
  from projects 
 where documentsFK = (
                      select documentFK 
                        from documents 
                       where documentsFK > 125
                     );

delete 
  from documents 
 where documentsFK > 125;

EDIT

delete 
  from projects 
 where documentsFK in (
                       select documentFK 
                         from documents 
                        where documentsFK > 125
                      );

delete 
  from documents 
 where documentsFK > 125;

Upvotes: 0

Oded
Oded

Reputation: 499142

When creating the foreign key, specify is as a ON DELETE CASCADE table constraint.

This constraint means that when a document is deleted, all project rows referencing it as a foreign key will also be deleted.

Upvotes: 6

Related Questions