1、MySql 外键设置详解 (1) 外键的使用: 外键的作用,主要有两个: 一个是让数据库自己通过外键来保证数据的完整性和一致性一个就是能够增加 ER 图的可读性 有些人认为外键的建立会给开发时操作数据库带来很大的麻烦.因为数据库有时候会由于没有通过外键的检测而使得开发人员删除,插入操作失败.他们觉得这样很麻烦 其实这正式外键在强制你保证数据的完整性和一致性.这是好事儿. 例如： 有一个基础数据表，用来记录商品的所有信息。其他表都保存商品 ID。查询时需要连表来查询商品的名称。单据 1 的商品表中有商品 ID 字段，单据 2 的商品表中也有商品 ID字段。如果不使用外键的话，当单据 1,2 都使
2、用了商品 ID=3 的商品时，如果删除商品表中ID=3 的对应记录后，再查看单据 1,2 的时候就会查不到商品的名称。 当表很少的时候，有人认为可以在程序实现的时候来通过写脚本来保证数据的完整性和一致性。也就是在删除商品的操作的时候去检测单据 1,2 中是否已经使用了商品 ID 为 3 的商品。但是当你写完脚本之后系统有增加了一个单据 3 ，他也保存商品 ID 找个字段。如果不用外键，你还是会出现查不到商品名称的情况。你总不能每增加一个使用商品 ID 的字段的单据时就回去修改你检测商品是否被使用的脚本吧，同时，引入外键会使速度和性能下降。(2) 添加外键的格式: ALTER TABLE you
3、rtablename 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: 外键表中外键字
4、段值会被更新,或所在的列会被删除. RESTRICT: RESTRICT 也相当于 no action,即不进行任何操作.即,拒绝父表 update 外键关联列,delete记录. set null: 被父面的外键关联字段被 update ,delete 时,子表的外键列被设置为 null. 而对于 insert,子表的外键列输入的值,只能是父表外键关联列已有的值.否则出错. 外键定义服从下列情况：(前提条件) 1) 所有 tables 必须是 InnoDB 型，它们不能是临时表.因为在 MySQL 中只有 InnoDB 类型的表才支持外键. 2) 所有要建立外键的字段必须建立索引. 3) 对
5、于非 InnoDB 表，FOREIGN KEY 子句会被忽略掉。 注意: 创建外键时,定义外键名时,不能加引号. 如: constraint fk_1 或 constraint “fk_1“是错误的 (3) 查看外键: SHOW CREATE TABLE *;可以查看到新建的表的代码以及其存储引擎.也就可以看到外键的设置. 删除外键: alter table drop foreign key 外键名. 注意: 只有在定义外键时,用 constraint 外键名 foreign key 方便进行外键的删除. 若不定义,则可以: 先输入:alter table drop foreign key 会
6、提示出错.此时出错信息中,会显示 foreign key 的系统默认外键名.- 用它去删除外键. (4) 举例实例一: 4.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 )
7、TYPE=INNODB; 向 parent 插入数据后,向 child 插入数据,插入时,child 中的 parent_id 的值只能是parent 中有的数据,否则插入不成功; 删除 parent 记录时,child 中的相应记录也会被删除;因为: on delete cascade 更新 parent 记录时,不给更新;因为没定义,默认采用 restrict. 4.2 若 child 如下: mysql create table child(id int not null primary key auto_increment,parent_id int, index par_ind (p
8、arent_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 co
9、nstraint fk_1 foreign key (parent_id) references parent(id) on update restrict on delete set null; (5) 多个外键存在: product_order 表对其它两个表有外键。 一个外键引用一个 product 表中的双列索引。另一个引用在 customer 表中的单行索引： CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id) TYPE=INNOD
10、B; 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; (6) 说明:1.若不声明 on update/delete,则默认是采用 restrict 方式. 2.对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.
