mysql delete and foreign key constraint

Question

I'm deleting selected rows from both table in MYSQL, the two tables have foreign keys.

DELETE d,b
  FROM A as b
  INNER JOIN B as d on b.bid=d.bid WHERE b.name LIKE '%xxxx%';

MYSQL complains about foreign keys even though I'm trying to delete from both tables:

Error: Cannot delete or update a parent row: a foreign key constraint
fails (`yyy/d`, CONSTRAINT `fk_d_bid` FOREIGN KEY (`bid`) REFERENCES 
`b` (`bid`) ON DELETE NO ACTION ON UPDATE NO ACTION)

what's the best solution here to delete from both table?

intgr · Accepted Answer · 2010-05-05 01:06:17Z

6

Change this constraint to use ON DELETE CASCADE -- which means that if a row is deleted, then any "child" rows will be automatically deleted as well.

Of course take good care of using CASCADE -- only use it when necessary. If you're overzealous with it, and accidentally do a well-placed DELETE, it might end up deleting half of your database. :)

See documentation on foreign key constraints.

edited May 5, 2010 at 1:06

answered May 5, 2010 at 0:47

intgr

20.7k6 gold badges63 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

laher · Accepted Answer · 2010-05-05 00:50:14Z

0

I think I see what you're trying to do

If you can't change the table structure, then you could use 2 statements, the first with a sub-select

delete from B where bid IN (select bid from A where name like '%xxxx%');

delete from A where name like '%xxxx%';

answered May 5, 2010 at 0:50

laher

9,1783 gold badges31 silver badges39 bronze badges

1 Comment

user121196 Over a year ago

great idea, but I got You can't specify target table 'B' for update in FROM clause

Collectives™ on Stack Overflow

mysql delete and foreign key constraint

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related