phpMyAdmin 中消失的外键
我正在 mysql 中创建一个新表，并尝试向其中一个字段添加外键约束。
CREATE TABLE `onlineorder` ( `receiptid` varchar(10) NOT NULL default '', `delivereddate` date default NULL, `cid` int(10) NOT NULL, `card#` int(10) default NULL, `expire` date default NULL, PRIMARY KEY (`receiptid`), FOREIGN KEY (receiptid) REFERENCES purchase ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
但是，在它创建之后，我进入 phpMyAdmin 并导出该表。 似乎外键约束已经消失了。
CREATE TABLE `onlineorder` ( `receiptid` varchar(10) NOT NULL default '', `delivereddate` date default NULL, `cid` int(10) NOT NULL, `card#` int(10) default NULL, `expire` date default NULL, PRIMARY KEY (`receiptid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
phpMyAdmin 是否删除了外键或者我在这里做错了什么？
I am creating a new table inside mysql and I am trying to add a foreign key constraint to one of the fields.
CREATE TABLE `onlineorder` ( `receiptid` varchar(10) NOT NULL default '', `delivereddate` date default NULL, `cid` int(10) NOT NULL, `card#` int(10) default NULL, `expire` date default NULL, PRIMARY KEY (`receiptid`), FOREIGN KEY (receiptid) REFERENCES purchase ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
However, after it creates it, I go into phpMyAdmin and export the table. and it seems like the foreign key constraint has disappeared.
CREATE TABLE `onlineorder` ( `receiptid` varchar(10) NOT NULL default '', `delivereddate` date default NULL, `cid` int(10) NOT NULL, `card#` int(10) default NULL, `expire` date default NULL, PRIMARY KEY (`receiptid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Does phpMyAdmin get rid of foreign keys or am I doing something wrong here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您需要使用InnoDB引擎来拥有外键。
参考： http://dev.mysql.com /doc/refman/5.0/en/innodb-foreign-key-constraints.html
You need to use the InnoDB engine to have foreign keys.
Ref: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
当您使用 MyISAM 存储引擎定义表时，它接受
FOREIGN KEY约束，但会默默地丢弃它们。 它会解析它们，但不会将约束存储在表元数据中，因此无法强制执行约束。 当您要求查看数据库对表定义的想法时，正如您所发现的那样，它对该约束一无所知。CHECK约束也会发生同样的情况（无论存储引擎如何）； 它解析语法并接受它，但随后忽略它。恕我直言，这对于 MySQL 产品来说是一件可怕的事情。 它接受标准 SQL，没有错误，给您留下这样的印象：它将以标准方式支持约束。 但事实并非如此！ 甚至
SHOW WARNINGS也没有显示 MySQL 已经忽略了该约束。如果您使用 InnoDB 存储引擎，它确实会注意外键约束。
更新：InnoDB 从 2019 年 4 月 25 日发布的 8.0.16 开始支持检查约束。
When you define a table with the MyISAM storage engine, it accepts
FOREIGN KEYconstraints, but it silently throws them away. It parses them, but does not store the constraint in the table metadata, and subsequently cannot enforce the constraint. When you ask to look at the database's idea of the table definition, it knows nothing about that constraint, as you have found out.The same thing happens with
CHECKconstraints (regardless of the storage engine); it parses the syntax and accepts it, but then ignores it.IMHO, this is a terrible thing for the MySQL product to do. It accepts standard SQL with no error, leaving you with the impression that it's going to support the constraint in the standard way. But it doesn't! Not even
SHOW WARNINGSreveals that MySQL has disregarded the constraint.If you use the InnoDB storage engine, it does heed the foreign key constraint.
Update: InnoDB supports check constraints as of 8.0.16, released 2019-04-25.
此外，PHPMyAdmin 会删除提取任何约束，直到创建所有表并插入数据为止。 如果您使用 PHPMySql 来转储表，您将在文件末尾找到 UPDATE 表部分，其中包含所有 ADD CONSTRAINT 条目。
Also, PHPMyAdmin removes extracts any CONSTRAINTS until after all the tables have been created and the data has been inserted. If you use as PHPMySql to dump the table for you, you'll find at the end of the file an UPDATE table section with all your ADD CONSTRAINT entries.
当您使用 phpmyadmin 管理表时，有两种类型的约束：
[视频：设置外键约束][1]
There are two type of constraints when you managing your tables with phpmyadmin:
[Video: Setting up a foreign key constraint][1]