MySQL自学笔记——设置外键约束

1.外键约束的要求：

a.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

b.数据表的存储引擎只能为InnoDB。

c.外键和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的

长度则可以不同。

d.外键列和参照列必须创建索引。

注意:
创建外键时,定义外键名时,不能加引号.
如: constraint 'fk_1' 或 constraint "fk_1"是错误的

2.外键的使用:
外键的作用:
a.让数据库自己通过外键来保证数据的完整性和一致性。

b.能够增加ER图的可读性。

c.实现一对一或一对多关系。

    有些人认为外键的建立会给开发时操作数据库带来很大的麻烦.因为数据库有时候会由于
没有通过外键的检测而使得开发人员删除,插入操作失败.他们觉得这样很麻烦，其实这正是外
键在强制你保证数据的完整性和一致性.
    例如：
    有一个基础数据表，用来记录商品的所有信息。其他表都保存商品ID。查询时需要连表来
查询商品的名称。单据1的商品表中有商品ID字段，单据2的商品表中也有商品ID字段。如果不
使用外键的话，当单据1,2都使用了商品ID=3的商品时，如果删除商品表中ID=3的对应记录后，
再查看单据1,2的时候就会查不到商品的名称。
    当表很少的时候，有人认为可以在程序实现的时候来通过写脚本来保证数据的完整性和一致
性。也就是在删除商品的操作的时候去检测单据1,2中是否已经使用了商品ID为3的商品。但是
当你写完脚本之后系统有增加了一个单据3 ，他也保存商品ID找个字段。如果不用外键，你还
是会出现查不到商品名称的情况。你总不能每增加一个使用商品ID的字段的单据时就回去修改

你检测商品是否被使用的脚本吧，同时，引入外键会使速度和性能下降。

3. 添加外键的格式:
ALTER  TABLE  tablename
    ADD [CONSTRAINT 外键名]  FOREIGN  KEY [id] (index_col_name, ...)
    REFERENCES  tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
说明:
on delete/on update,用于定义delete,update操作.以下是update,delete操作的各种约束类型:
CASCADE:
从父表中删除或更新行时，自动删除或更新子表中匹配的行。
RESTRICT:
RESTRICT也相当于no action,即不进行任何操作.即拒绝对父表的删除或更新操作.
set null:
从父表中删除或更新行时,子表的外键列被自动设置为null.
注意：如果使用该选项，必须保证子表中没有指定NOT NULL.

4.查看外键:
SHOW  CREATE  TABLE tablename;可以查看到新建的表的代码以及其存储引擎.也就可以看到外键的设置.
删除外键:
alter table drop foreign key '外键名'.
注意:
只有在定义外键时,用constraint 外键名 foreign key .... 方便进行外键的删除.
若不定义,则可以:
先输入:alter table drop foreign key -->会提示出错.此时出错信息中,会显示foreign key的系统默认外键名.--->

用它去删除外键.

5.举例
实例一:
5.1
CREATE TABLE parent(id INT NOT NULL,
                    PRIMARY KEY (id)
) TYPE=INNODB;                      -- type=innodb 相当于 engine=innodb
CREATE TABLE child(id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) TYPE=INNODB;
向parent插入数据后,向child插入数据,插入时,child中的parent_id的值只能是parent中有的数据,否则插入不成功;
删除parent记录时,child中的相应记录也会被删除;-->因为: on delete cascade

更新parent记录时,不给更新;-->因为没定义,默认采用restrict.

5.2
若child如下:
mysql>
create table child(id int not null primary key auto_increment,parent_id int,
index par_ind (parent_id),
constraint fk_1 foreign key (parent_id) references
parent(id) on update cascade on delete restrict)
type=innodb;
用上面的:
1).
则可以更新parent记录时,child中的相应记录也会被更新;-->因为: on update cascade
2).
不能是子表操作,影响父表.只能是父表影响子表.
3).
删除外键:
alter table child drop foreign key fk_1;
添加外键:
alter table child add constraint fk_1 foreign key (parent_id) references
parent(id) on update restrict on delete set null;

6. 多个外键存在:
product_order表对其它两个表有外键。
一个外键引用一个product表中的双列索引。另一个引用在customer表中的单行索引：
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      -- 双外键
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      -- 单外键
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                       REFERENCES customer(id)) TYPE=INNODB;
 说明:
1.若不声明on update/delete,则默认是采用restrict方式.
2.对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.