How to Delete Rows in Tables That Contain Foreign Keys to Other Tables

Mysql: delete rows in two tables with foreign keys

Please try this, hope it will help.

DELETE FROM departure, departure_time
USING departure
INNER JOIN departure_time
WHERE departure_date = '2016-09-30'
      AND departure_time.id = departure.id

Or

DELETE FROM departure, departure_time
USING departure
INNER JOIN departure_time
WHERE departure_date = '2016-09-30'
      AND departure_time.departure_id = departure.id

Or you can use ON DELETE CASCADE that will do work automatically for you .

How to delete rows in tables that contain foreign keys to other tables

SET NULL instead of CASCADE.

Delete from 2 tables with foreign key constraints

As the comments said all you need to do is flip your delete statements and you should be good:

You may consider wraping them in a begin tran so you can check that your deletes only delete the data you want as well:

  Begin Tran
    DECLARE @INVOICEID INT
    SET @INVOICE = (select InvoiceID from Rel_Inv_Course where CourseID=4262)

        delete from Rel_Inv_Course where CourseID=4262

        delete from [TAP].[dbo].Invoice where InvoiceID =(@INVOICEID)

    --Select * from Rel_Inv_Course 
    --Select * from [dbo].Invoice 

    --If satisfied with deletes finally commit tran
    --If not satisfied --> Rollback Tran
    Commit Tran

SQL Server : Delete rows based on foreign key

As mentioned by @SOS in the comments, you can use the OUTPUT clause when deleting from B to capture any relevant IDs for A. You then delete any associated A records, but exclude any that are still associated to another B record:

DECLARE @tmp TABLE (ID_A);

DELETE b
FROM B b
OUTPUT deleted.ID_A
  INTO @tmp (ID_A)
WHERE b.ID_C = 'c1';

DELETE a
FROM A a
WHERE a.ID_A IN (
    SELECT t.ID_A
    FROM @tmp t
    EXCEPT
    SELECT b.ID_A
    FROM B b
);

DELETE c
FROM C c
WHERE c.ID_C = 'c1';

Delete data that contains foreign key

You want on delete cascade as an option in the foreign key constraint in the children table.

Say that table groups looks like:

create table groups (
    id int primary key,
    ... -- other columns here
);

You would then declare child table user_groups as:

create table user_groups (
    ... -- some columns here
    group_id int,
    foreign key (group_id) references groups(id) on delete cascade
)

---

Related Topics