in mysql, on delete cascade not working

Question

similar to ON DELETE CASCADE not working in MySQL, but something is not right:

The ANSI Way

-- test delete cascade
CREATE TABLE t1(
    id SERIAL PRIMARY KEY,
    data TEXT
);

CREATE TABLE t2(
    id INT PRIMARY KEY REFERENCES t1(id) ON DELETE CASCADE,
    data2 TEXT
);

INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t2 VALUES(1, 'first');

DELETE FROM t1;
SELECT * FROM t2; -- should have not rows - have one!

use this all the time in postgres, but for some reason cannot get it going in mysql.

I am slowly learning, there is the ansi-standard, postgreql way, and there is the mysql way. Each time I think I have somewhat appreciated the difference, I haven't come close.

The MySQL Way

CREATE TABLE `t2` (
    `id` BIGINT(20) UNSIGNED NOT NULL,
    `data2` TEXT,
    PRIMARY KEY (`id`),
    CONSTRAINT `FK_t2_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

To me, the code I have is ansi standard, makes perfect sense, and is (as far as SQL goes) aesthetically pleasing, whereas, the mysql way (thanks for the help!) reminds me of Visual Basic or something - it's really ugly as sin and imho it's wrong to ask intelligent people to debase themselves to write such a thing.

I apologize if ranting, and justly deserve any number of negative ratings. You guys who write this code with ease have my greatest respect. I just hate to see this sort of meaningless punishment inflicted on friends ;-)

Can you edit your question to indicate that the second part is the answer and not part of your question? I was confused as to whether that MySql code worked or not, since at first glance it seems to be part of the question. Add a "Solution" heading or something. — Matthias, Jun 10 '15 at 16:39
@MatthiasDailey - the point is the second way works but is a kludge. If a table is declared `on delete cascade` is should indeed delete on cascade when it is created, or throw an error and not create the table. the worst behavior is to specify a business rule and that business rule _not_ be instantiated, leaving you hung out to dry when you think you're protected. — cc young, Jun 12 '15 at 15:37

dnagirl · Accepted Answer · 2011-09-06T13:54:02.933

8

If you create t2 like this it works fine:

CREATE TABLE  `t2` (
  `id` bigint(20) unsigned NOT NULL,
  `data2` text,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_t2_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ETA, in answer to concerns about ugly code, the below also works:

CREATE TABLE  t2 (
  id bigint(20) unsigned NOT NULL PRIMARY KEY,
  data2 text,
  CONSTRAINT  FOREIGN KEY (id) REFERENCES t1(id) ON DELETE CASCADE
) ENGINE=InnoDB ;

The main difference is that the data type for t2.id must match that of t1.id and constraints have to be declared after the columns.

edited Sep 06 '11 at 13:54

answered Sep 06 '11 at 13:06

dnagirl

20,196
13
80
123

3

this works - thanks! - but holy mother of god I don't see how you guys program this way - you really have my respect! did no know how painful (for me!) going to mysql would be. – cc young Sep 06 '11 at 13:24
2

@cc young: I've posted a prettier version to show that MySQL doesn't have to look like apostrophe soup. I don't usually backtick all my column and table names because leaving them off enforces good naming practices (like not using reserved words or spaces). But most MySQL code generators put in the backticks. – dnagirl Sep 06 '11 at 13:56
I feel much better about your pinkie not having to grab those backticks, but I still feel frustrated that mysql cannot be ansi compliant. thanks again! – cc young Sep 06 '11 at 14:09
1

This answer works, but it doesn't explain _why_ it works. As @a_horse_with_no_name explains, MySQL ignores all inline foreign key constraints and doesn't throw an error. The [MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/create-table.html) explain that if you know where to look (start where it says "MySQL parses but ignores “inline `REFERENCES` specifications”"). – teuber789 Mar 16 '20 at 17:57

score 5 · Answer 2 · answered Jan 17 '17 at 07:07

5

Set the foreign_key_checks to 1, I ran into this problem while exporting and importing the data during which it was set to 0

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1 */;

answered Jan 17 '17 at 07:07

zillani

1,070
3
18
26

score 5 · Answer 3 · edited Mar 16 '20 at 18:17

5

(assuming that should be a "foreign key" not a "primary key" in table t2)

MySQL simply ignores all inline foreign key constraints without a warning.

Therefore, you need to explicitly add a foreign key constraint as shown by dnagirl.

edited Mar 16 '20 at 18:17

teuber789

1,527
16
28

answered Sep 06 '11 at 13:08

score 2 · Answer 4 · edited Apr 07 '20 at 05:09

This happens because the default storage engine 'MyISAM' does not support foreign keys!

Simply set the engine to InnoDB and make the referencing and referenced columns definition of both tables are matched.

And this is an example:

CREATE TABLE `students` (
  `id` INT AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `age` TINYINT NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB CHARSET=latin1;

CREATE TABLE `marks` (
  `student_id` INT PRIMARY KEY,
  `mark` DECIMAL(5,2) NOT NULL,
  CONSTRAINT marks_FK_1 FOREIGN KEY(`student_id`) REFERENCES students(`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=latin1;

in mysql, on delete cascade not working

The ANSI Way

The MySQL Way

4 Answers4

Linked

Related