In mysql, when deleting a cascade does not work

Question

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

ANSI path

-- 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 in mysql.

---

I am slowly involved, there is ansi-standard, postgreql, and there is a mysql way. Every time I think I appreciated the difference somewhat, I did not come close.

MySQL path

 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; 

For me, the code I have is ansi standard, makes perfect sense and (as far as SQL is concerned) is aesthetically pleasing, while the mysql-way (thanks for the help!) Reminds me of Visual Basic or something else - it's really ugly as a sin, and it’s not good to ask intelligent people to humiliate themselves in order to write such a thing.

I apologize if I ranted, and rightly deserved any number of negative ratings. You guys who easily write this code really respect me. I just don't want to see this kind of meaningless punishment on friends; -)

Answer 1

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 response to concerns about ugly code, the following 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 the declaration of t1.id, and restrictions must be declared after the columns.

Answer 2

(it is assumed that this should be a "foreign key", not a "primary key" in table t2)

MySQL simply ignores all the built-in foreign key constraints without warning.

For this you need to explicitly add an alien as shown by dnagirl

Answer 3

Set the value of foreign_key_checks to 1, I encountered this problem when exporting and importing data during which it was set to 0

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