SQLTeam.com | Weblogs | Forums

Deleting records and associated records


#1

Hi There,

In need of your help again.

I have to delete some records to make room on the host.
I have 2 tables, Rentals & Invoices.
I would like to delete records from the Rentals table and also delete the related invoice record in the Invoices table.
In my Rentals table I have an InvoiceId column that relates to the record in the Invoices Table.
How can I select records from 13/06/2012 to 13/06/2015 in the Rentals Table also deleting the associated records in the Invoice Table.

Your help is appreciated as always.

Best Regards,

Steve.


#2

If you are created foreign key on those tables. Foreign key has option to delete the relative values when it deleted in the primary key table.

ALTER TABLE ReferencingTable DROP 
   CONSTRAINT fk__ReferencingTable__MainTable;

ALTER TABLE ReferencingTable ADD 
   CONSTRAINT fk__ReferencingTable__MainTable 
      FOREIGN KEY (pk_col_1)
      REFERENCES MainTable (pk_col_1)
      ON DELETE CASCADE;

Please find the detailed explanation in the below link
https://msdn.microsoft.com/en-in/library/ms189049.aspx