需要 MYSQL 外键强制执行
考虑以下 SQL：
CREATE DATABASE TEST01234;
USE TEST01234;
CREATE TABLE PARENT (
PARENT_NAME varchar(255) PRIMARY KEY,
PARENT_DESC varchar(255));
CREATE TABLE CHILD (
PARENT_NAME varchar(255),
CHILD_NAME varchar(255),
CHILD_DESC varchar(255),
PRIMARY KEY (PARENT_NAME, CHILD_NAME),
FOREIGN KEY (PARENT_NAME) REFERENCES PARENT(PARENT_NAME)
ON UPDATE CASCADE
ON DELETE CASCADE);
INSERT INTO PARENT VALUES("PARENT A","Some Parent");
INSERT INTO CHILD VALUES("PARENT A","CHILD A","Some Child");
INSERT INTO CHILD VALUES("PARENT A","CHILD B","Some Child");
INSERT INTO CHILD VALUES("PARENT A","CHILD C","Some Child");
INSERT INTO CHILD VALUES("PARENT B","CHILD D","Non-present Parent");
DELETE FROM PARENT WHERE PARENT_NAME = "PARENT A";
SELECT * FROM CHILD;
另外，
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.41-3ubuntu12.10 |
| version_comment | (Ubuntu) |
| version_compile_machine | i486 |
| version_compile_os | debian-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.00 sec)
发生了两件我认为会有所不同的事情。
* 首先，MySQL 允许我向 CHILD 中插入与 PARENT 中任何内容都不匹配的值。我认为外键限制会阻止这种情况发生。
* 第二个是当从 PARENT 中删除 PARENT A 时，MySQL 不会删除 CHILD 中的 PARENT A 记录>。我认为级联声明可以实现这一点。
我觉得我错过了一些明显的东西。有什么建议吗？
Consider the following SQL:
CREATE DATABASE TEST01234;
USE TEST01234;
CREATE TABLE PARENT (
PARENT_NAME varchar(255) PRIMARY KEY,
PARENT_DESC varchar(255));
CREATE TABLE CHILD (
PARENT_NAME varchar(255),
CHILD_NAME varchar(255),
CHILD_DESC varchar(255),
PRIMARY KEY (PARENT_NAME, CHILD_NAME),
FOREIGN KEY (PARENT_NAME) REFERENCES PARENT(PARENT_NAME)
ON UPDATE CASCADE
ON DELETE CASCADE);
INSERT INTO PARENT VALUES("PARENT A","Some Parent");
INSERT INTO CHILD VALUES("PARENT A","CHILD A","Some Child");
INSERT INTO CHILD VALUES("PARENT A","CHILD B","Some Child");
INSERT INTO CHILD VALUES("PARENT A","CHILD C","Some Child");
INSERT INTO CHILD VALUES("PARENT B","CHILD D","Non-present Parent");
DELETE FROM PARENT WHERE PARENT_NAME = "PARENT A";
SELECT * FROM CHILD;
Also,
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.41-3ubuntu12.10 |
| version_comment | (Ubuntu) |
| version_compile_machine | i486 |
| version_compile_os | debian-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.00 sec)
Two things are happening that I thought would be different.
* The first is that MySQL lets me insert values into CHILD that don't match anything in PARENT. I thought the foreign key restraint would stop that from happening.
* The second is that MySQL doesn't delete the PARENT A records from CHILD when PARENT A is deleted from PARENT. I thought that the cascade statement would make that happen.
I feel like I'm missing something obvious. Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要指定表使用 InnoDB 引擎。默认情况下，MySQL 将使用 MyISAM 表，它理解“创建表”查询中的外键规范，但忽略键本身。
如果您执行“show create table PARENT”，您很可能会看到
ENGINE=MyISAM。You need to specify using the InnoDB engine for the tables. By default MySQL will use MyISAM tables, which understand foreign key specifications in "create table" queries, but ignore the keys themselves.
If you do a 'show create table PARENT', you'll most likely see
ENGINE=MyISAM.你用的是InnoDB吗？ MyISAM（MySQL 中的默认引擎）不支持外键，而 InnoDB 支持。
Are you using InnoDB? MyISAM (the default engine in MySQL) does not support foreign keys, whereas InnoDB does.