在mysql中，在删除级联上不起作用 [英] in mysql, on delete cascade not working

问题描述

类似于 ON DELETE CASCADE在MySQL中不起作用，但是某些事情不正确:

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

-- 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!

在postgres中一直使用 ，但是由于某种原因无法在mysql中使用它.

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

我正在慢慢学习，有ansi-standard，postgreql方法和有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.

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;

对我来说，我拥有的代码是ansi标准，非常有意义，并且(就SQL而言)在美学上令人愉悦，而mysql方式(感谢帮助！)使我想起了Visual Basic或其他一些东西-罪恶和恕我直言真是丑陋，要求有智慧的人贬低自己写这样的东西是错误的.

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

推荐答案

如果您这样创建t2，则效果很好:

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，针对对丑陋代码的担忧，以下内容也适用:

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 ;

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

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.

这篇关于在mysql中，在删除级联上不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！