(1) 外键的使用:
外键的作用,主要有两个:
一个是让数据库自己通过外键来保证数据的完整性和一致性
一个就是能够增加ER图的可读性
有些人认为外键的建立会给开发时操作数据库带来很大的麻烦.因为数据库有时候会由于没有通过外键的检测而使得开发人员删除，插入操作失败.其实这正式外键在强制保证数据的完整性和一致性.
(2) 添加外键的格式:
ALTER TABLE yourtablename
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,即不进行任何操作.即,拒绝父表update外键关联列,delete记录.
set null:
被父表的外键关联字段被update ,delete时,子表的外键列被设置为null.
而对于insert,子表的外键列输入的值,只能是父表外键关联列已有的值.否则出错.
外键定义服从下列情况：(前提条件)
1) 所有tables必须是InnoDB型，它们不能是临时表.因为在MySQL中只有InnoDB类型的表才支持外键.
2) 所有要建立外键的字段必须建立索引.
3) 对于非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 -->会提示出错.此时出错信息中,会显示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
) 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 (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;
4.3
CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1.向parent插入数据后,向child插入数据,插入时,child中的parent_id的值只能是parent中有的数据,否则插入不成功;
2.删除parent记录时，删除无法成功。
3.删除child中的记录时。parent的记录不会被删除。
4.修改也不能成功
(5) 多个外键存在:
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;
(6) 说明:
1.若不声明on update/delete,则默认是采用restrict方式.
2.对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.
3.查看一个表结构语句show CREATE TABLE child