使用PHPMyAdmin创建外键约束

设计器(图形化)方式编辑:

http://www.yourwebskills.com/dbphpmyadminrelationships.php

手动方式编辑:

选择表–>结构–>关系查看(位于表结构下方)

约束关系简介:
http://stackoverflow.com/a/11894393

This is a summary of a Wikipedia article. It specifies the different types of relationships you can stipulate in PHPmyadmin. I am putting it here because it is relevant to @Nathan’s comment on setting the foreign keys options for “on update/delete” but is too large for a comment – hope it helps.

CASCADE

主键被删除/更新时,外键自动被删除/更新
Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).

RESTRICT

主键不允许被删除/更新
A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

跟RESTRICT非常类似,只是判断的细节不同
NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

SET NULL

主键被删除/更新时,外键自动被设成NULL
The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

SET DEFAULT

主键被删除/更新时,外键自动被设成默认值。这条在创建关系时并没有看到有得选,也许什么都不选就表示这个选项?
Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.

注意点:两张表都必须是Inno DB引擎。referencing field必须创建索引。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.