How to delete from multiple tables in MySQL with JOIN?

Question

I'm trying to run this delete script:

DELETE auth, cust
FROM customer cust 
JOIN authentication_token auth ON auth.customer_id = cust.id
WHERE cust.email = '[email protected]';

I'm getting the error:

Cannot delete or update a parent row: a foreign key constraint fails (`my_table`.`authentication_token`, CONSTRAINT `authentication_token_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`))

I've tried to change the order of the DELETE statement (cust, auth), and I've tried to change the order of the join, but still the same error.

Is this possible, or will I have to temporary disable constraints?

Table structure:

CREATE TABLE `customer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `passwordSalt` varchar(255) DEFAULT NULL,
  `phoneNumber` varchar(255) DEFAULT NULL,
  `addressLine1` varchar(255) DEFAULT NULL,
  `addressLine2` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `county` varchar(255) DEFAULT NULL,
  `postCode` varchar(255) DEFAULT NULL,
  `registeredOn` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `authentication_token` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `customer_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `authentication_token_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
)

can you share parent and child table structure ?

Ravi
– Ravi

2018-01-22 14:23:28 +00:00
Commented Jan 22, 2018 at 14:23 — Ravi
– Ravi, Commented Jan 22, 2018 at 14:23
@Ravi updated question.

user7862512
– user7862512

2018-01-22 14:29:09 +00:00
Commented Jan 22, 2018 at 14:29 — user7862512
– user7862512, Commented Jan 22, 2018 at 14:29

walter · Accepted Answer · 2018-01-22 14:39:15Z

1

Alter your table definition:

CREATE TABLE `authentication_token` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `customer_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `authentication_token_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE
)

And then just run this SQL:

DELETE FROM cust WHERE cust.email = '[email protected]';

When you delete the records from cust, the relevant records in authentication_token will be deleted automatically.

edited Jan 22, 2018 at 14:39

answered Jan 22, 2018 at 14:33

walter

1,2698 silver badges14 bronze badges

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

1 Comment

user7862512 Over a year ago

That's great! I didn't know about automatic cascaded deletes. That's a great solution, much better than what I was trying to do. Thank you!

Ravi · Accepted Answer · 2018-01-22 14:38:17Z

0

Cannot delete or update a parent row

It means, you can't update/delete parent table, since, reference still exist in some other child table(s).

If you wanted to delete from both table, then best solution would be altering your table to add

ON CASCADE DELETE

It will ensure that all the references rows gets deleted.

CONSTRAINT `authentication_token_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON CASCADE DELETE

edited Jan 22, 2018 at 14:38

answered Jan 22, 2018 at 14:28

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

3 Comments

user7862512 Over a year ago

I'm not specifying columns, I'm specifying tables.

Ravi Over a year ago

@user7862512 DELETE auth, cust are they table ?

user7862512 Over a year ago

yep. They're tables which I've aliased in the from and join statement.

Collectives™ on Stack Overflow

How to delete from multiple tables in MySQL with JOIN?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related