内容整理自:
http://hi.baidu.com/shanghaocool/item/ba5ac2a4c276fed35bf191ce
http://www.cnblogs.com/xiangxiaodong/archive/2013/05/05/3061049.html
外键的使用:
外键的作用,主要有两个:
一个是让数据库自己通过外键来保证数据的完整性和一致性
一个就是能够增加ER图的可读性
有些人认为外键的建立会给开发时操作数据库带来很大的麻烦.因为数据库有时候会由于没有通过外键的检测而使得开发人员删除,插入操作失败.他们觉得这样很麻烦
其实这正式外键在强制你保证数据的完整性和一致性.这是好事儿.
例如：
有一个基础数据表，用来记录商品的所有信息。其他表都保存商品ID。查询时需要连表来查询商品的名称。单据1的商品表中有商品ID字段，单据2的商品表中也有商品ID字段。如果不使用外键的话，当单据1,2都使用了商品ID=3的商品时，如果删除商品表中ID=3的对应记录后，再查看单据1,2的时候就会查不到商品的名称。
当表很少的时候，有人认为可以在程序实现的时候来通过写脚本来保证数据的完整性和一致性。也就是在删除商品的操作的时候去检测单据1,2中是否已经使用了商品ID为3的商品。但是当你写完脚本之后系统有增加了一个单据3 ，他也保存商品ID找个字段。如果不用外键，你还是会出现查不到商品名称的情况。你总不能每增加一个使用商品ID的字段的单据时就回去修改你检测商品是否被使用的脚本吧，同时，引入外键会使速度和性能下降。
为已经添加好的数据表添加外键:
语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)
//FK_ID是外键的名称
/*
CREATE TABLE `tb_active` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`),
CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/
删除外键
语法： ALTER TABLE table-name DROP FOREIGN KEY key-id;
例： ALTER TABLE `tb_active` DROP FOREIGN KEY `FK_ID`
自动键更新和删除：
外键可以保证新插入的记录的完整性，但是，如果在REFERENCES从句中已命名的表删除记录会怎么样？在使用同样的值作为外键的辅助表中会发生什么?
很明显，那些记录也应该被删除，否则在数据库中就会有很多无意义的孤立记录，MYSQL可以通过向FOREIGN KEY...REFERENCES修饰符添加一个ON
DELETE
或ON UPDATE子句简化任务，它告诉了数据库在这种情况如何处理孤立任务
关键字 含义
CASCADE 删除包含与已删除键值有参照关系的所有记录
SET NULL 修改包含与已删除键值有参照关系的所有记录，使用NULL值替换（只能用于已标记为NOT NULL的字段）
RESTRICT 拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
NO ACTION 啥也不做
请注意，通过ON UPDATE 和 ON
DELETE
规则，设置MYSQL能够实现自动操作时，如果键的关系没有设置好，可能会导致严重的数据破坏，
例如：如果一系列的表通过外键关系和ON
DELETE
CASCADE 规则连接时，任意一个主表的变化都会导致甚至只和原始删除有一些将要联系的记录在没有警告的情况被删除，所以，我们在操作之前还要检查这些规则的，操作之后还要再次检查.
添加外键
alter table locstock add foreign key locstock_ibfk2(stockid) references product(stockid)
locstock 为表名, locstock_ibfk2 为外键名 第一个括号里填写外键列名, product为表名,第二个括号里是写外键关联的列名
删除外键
alter table locstock drop foreign key locstock_ibfk2
查看表有哪些外键
show create table locstock
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON
DELETE
{RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
所有tables必须是InnoDB型 ，它们不能是临时表。
在引用表中，必须有一个索引，外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在，它必须在引用表里被自动创建。
在引用表中，必须有一个索引，被引用的列以同样的顺序被列在其中作为第一列。
不支持对外键列的索引前缀。这样的后果之一是BLOB和TEXT列不被包括在一个外键中， 这是因为对这些列的索引必须总是包含一个前缀长度。
如果CONSTRAINTsymbol 被给出，它在数据库里必须是唯一的。如果它没有被给出，InnoDB自动创建这个名字。
添加外键的格式:
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"是错误的
查看外键:
SHOW CREATE TABLE ***;可以查看到新建的表的代码以及其存储引擎.也就可以看到外键的设置.
删除外键:
alter table drop foreign key '外键名'.
注意:
只有在定义外键时,用constraint 外键名 foreign key .... 方便进行外键的删除.
若不定义,则可以:
先输入:alter table drop foreign key -->会提示出错.此时出错信息中,会显示foreign key的系统默认外键名.--->
用它去删除外键.
举例
实例一:
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;
多个外键存在:
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 的方式.
