MySQL之外键约束

ゞ浴缸里的玫瑰 2022-08-06 02:04 176阅读 0赞

一、基本概念

1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

3、如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

4、外键的使用条件

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键

② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

5、外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

二、使用方法

1、创建外键的语法：

外键的定义语法：

\[CONSTRAINT symbol\] FOREIGN KEY \[id\] (index\_col\_name, ...)

REFERENCES tbl\_name (index\_col\_name, ...)

\[ON DELETE \{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT\}\]

\[ON UPDATE \{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT\}\]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

ON DELETE、ON UPDATE表示事件触发限制，可设参数：

① RESTRICT（限制外表中的外键改动，默认值）

② CASCADE（跟随外键改动）

③ SET NULL（设空值）

④ SET DEFAULT（设默认值）

⑤ NO ACTION（无动作，默认的）

2、示例

1）创建表1

create table repo\_table(

repo\_id char(13) not null primary key,

repo\_name char(14) not null)

type=innodb;

创建表2

mysql> create table busi\_table(

-> busi\_id char(13) not null primary key,

-> busi\_name char(13) not null,

-> repo\_id char(13) not null,

-> foreign key(repo\_id) references repo\_table(repo\_id))

\-> type=innodb;

2）插入数据

insert into repo\_table values("12","sz"); //success

insert into repo\_table values("13","cd"); //success

insert into busi\_table values("1003","cd", "13"); //success

insert into busi\_table values("1002","sz", "12"); //success

insert into busi\_table values("1001","gx", "11"); //failed,提示：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (\`smb\_man\`.\`busi\_table\`, CONSTRAINT \`busi\_table\_ibfk\_1\` FOREIGN KEY (\`repo\_id\`) REFERENCES \`repo\_table\` (\`repo\_id\`))

3）增加级联操作

mysql> alter table busi\_table

-> add constraint id\_check

-> foreign key(repo\_id)

-> references repo\_table(repo\_id)

-> on delete cascade

-> on update cascade;

\-----

ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一种方法，可以替换type=innodb;

3、相关操作

外键约束（表2）对父表（表1）的含义:

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。

<table style="margin:0px; padding:0pt 5.4pt; border-collapse:collapse; border-spacing:0px; border:1px solid silver; color:rgb(0,0,0); font-family:Verdana,Arial,Helvetica,sans-serif; font-size:14px; line-height:20px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> 关键字 </td> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> 含义 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> CASCADE </td> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> 删除包含与已删除键值有参照关系的所有记录 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> SET NULL </td> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> 修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段) </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> RESTRICT </td> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> 拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置) </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> NO ACTION </td> 
 <td style="margin:0px; padding:0pt 5.4pt; border:1px solid silver; border-collapse:collapse"> 啥也不做 </td> 
 </tr> 
 </tbody> 
</table>

4、其他

在外键上建立索引:

index repo\_id (repo\_id),

foreign key(repo\_id) references repo\_table(repo\_id))

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

MySQL有两种常用的引擎类型：MyISAM和InnoDB。**目前只有InnoDB引擎类型支持外键约束**。InnoDB中外键约束定义的语法如下：

\[CONSTRAINT \[symbol\]\] FOREIGN KEY  
    \[index\_name\] (index\_col\_name, ...)  
    REFERENCES tbl\_name (index\_col\_name,...)  
    \[ON DELETE reference\_option\]  
    \[ON UPDATE reference\_option\]

reference\_option:  
    RESTRICT | CASCADE | SET NULL | NO ACTION  外键的使用需要满足下列的条件：

1. 两张表必须都是InnoDB表，并且它们没有临时表。

2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

3. 建立外键关系的对应列必须建立了索引。

4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。**假如没有显式的给出，InnoDB会自动的创建。**

如果**子表**试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果**父表**试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:

1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

3. NO ACTION: InnoDB拒绝删除或者更新父表。

4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

5. SET DEFAULT: InnoDB目前不支持。

**  外键约束使用最多的两种情况无外乎：**

1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；

2）父表更新时子表也更新，父表删除时子表匹配的项也删除。

前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。

InnoDB允许你使用ALTER TABLE在一个已经存在的表上增加一个新的外键:

ALTER TABLE tbl\_name  
    ADD \[CONSTRAINT \[symbol\]\] FOREIGN KEY  
    \[index\_name\] (index\_col\_name, ...)  
    REFERENCES tbl\_name (index\_col\_name,...)  
    \[ON DELETE reference\_option\]  
    \[ON UPDATE reference\_option\]

InnoDB也支持使用ALTER TABLE来删除外键：  
ALTER TABLE tbl\_name DROP FOREIGN KEY fk\_symbol;

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

MySQL对此一直持观望态度，它允许使用外键，但是为了完整性检验的目的，在除了InnoDB表类型之外的所有表类型中都忽略了这个功能。这可能有些怪异，实际上却非常正常：对于数据库的所有外键的每次插入、更新和删除后，**进行完整性检查是一个耗费时间和资源的过程，它可能影响性能**，特别是当处理复杂的或者是缠绕的连接树时。因而，用户可以在表的基础上，选择适合于特定需求的最好结合。。

所以，如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

注意事项：

*  一旦建立外键，MySQL只允许向当前表中加入外键表中已有的数据列。比如说贪官表里有“王二麻子”，那么在情妇表只才能有“王二麻子的情妇”。也就是说只有确认一个人是贪官了，才能把其情妇信息列入此表中，否则是不行滴。
 *  关系中的所有表必须是innoDB表，在非InnoDB表中，MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。
 *  用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。
 *  在外键关系中，字段的数据类型必须相似，这对于大小和符号都必须匹配的整数类型尤其重要。
 *  即使表存在外键约束，MySQL还允许我们删除表，并且不会产生错误(即使这样做可能会破坏更早创建的外键)