在mysql中，删除级联不起作用

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

类似于ON DELETE CASCADE在MySQL中不起作用,但有些事情是不对的:

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.

在postgres中一直使用这个,但由于某种原因无法在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.

我正在慢慢学习,有ansi标准,postgreql方式,还有mysql方式。每次我觉得我有点赞赏这种差异,我都没有接近。

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.

对我来说,我的代码是ansi标准,非常有意义,并且(就SQL而言)美观,而mysql方式(感谢帮助!)让我想起Visual Basic或其他东西 - 它真的很难看作为罪和imho,要求聪明的人贬低自己写这样的东西是错误的。

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 ;-)

如果咆哮,我道歉,并且应该得到任何数量的负面评价。你们轻松编写这些代码的人对我非常尊重。我只是讨厌在朋友身上看到这种毫无意义的惩罚;-)

3 个解决方案

#1

If you create t2 like this it works fine:

如果你像这样创建t2它工作正常:

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:

ETA,为了回答对丑陋代码的担忧,下面也有效:

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.

主要区别在于t2.id的数据类型必须与t1.id的数据类型匹配,并且必须在列之后声明约束。

#2

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

(假设这应该是“外键”而不是表t2中的“主键”)

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

MySQL只是忽略所有内联外键约束而没有警告。

Therefor you need to explicitely add a foreign as shown by dnagirl

因此,你需要明确地添加一个外国人,如dnagirl所示

#3

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

将foreign_key_checks设置为1,我在导出和导入数据时遇到了这个问题,在此期间它被设置为0

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

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

#1