MYSQL外键

请注意 :
1、外键约束只能在innodb引擎下使用。
2、外键应该在建立子表的时候建立。
3、外键所建立的字段，其字段类型应该与父表中对应的字段类型相同。例如同为INT。若不同在建立外键时会报一个150的错误码。

下面我们先来创建一张主表

mysql> create table grades(    -> id tinyint not null auto_increment primary key comment '自增主键',    -> grade_name varchar(40) not null comment '年级名称'    -> )engine=innodb default charset=utf8 comment '年级表';Query OK, 0 rows affected (0.00 sec)mysql> desc grades;+------------+-------------+------+-----+---------+----------------+| Field      | Type        | Null | Key | Default | Extra          |+------------+-------------+------+-----+---------+----------------+| id         | tinyint(4)  | NO   | PRI | NULL    | auto_increment || grade_name | varchar(40) | NO   |     | NULL    |                |+------------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)

可以看到我们已经成功创建了一张年级表，接下来我们再创建一张学生表，并且为学生表创建外键，关联年级表的id字段。

注意关键语法：constraint 外键名 foreign key(需要设置外键的字段) references 父表(父表关联的字段)

mysql> create table students (    -> id int not null auto_increment primary key comment '自增id',    -> name varchar(45) not null comment '学生姓名',    -> g_id tinyint not null comment '年级ID 后面会设置外键约束',    -> constraint fk_grade_id foreign key(g_id) references grades(`id`)    -> )engine=innodb default charset=utf8 comment '设置了外键约束的学生表';Query OK, 0 rows affected (0.01 sec)

设置外键联动

但是我们仅仅是建立了外键，并没有指定它的联动关系，所以这个外键实际上是并没有什么用处的。
外键的联动可以通过on delete和on update来触发。

触发的效果有三种：
1、cascade关联操作，如果主表被更新或删除，从表也会执行相应的操作
2、set null,表示从表数据不指向主表任何记录 即当主表数据更新后不存在时将从表的外键值设为空
3、restrict：拒绝主表的相关操作 即主表没有的数据 都不能添加或更新

设置外键的正确姿势应该是constraint 外键名 foreign key(需要设置外键的字段) references 父表(父表关联的字段) on delete/update cascade/restrict/set null ;

删除错误的外键

我们现在来删除一开始创建的并没有什么卵用的外键

mysql> alter table students    -> drop foreign key     -> fk_grade_id;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

在已有的表上创建一个新的外键

mysql> alter table students  add constraint fk_id foreign key(g_id) references grades(`id`) on update restrict on delete cascade;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

注意：on delete 时不能设置动作为 set null 否则会报一个ERROR 1005 (HY000): Can’t create table ‘demo.#sql-1fcc_3’ (errno: 150)的错误

解释一下上面的sql：修改表students ，新增一个外键约束，外键约束的名称是
fk_id,关联的父表是grades关联键名是父表下的id设置约束为，更新操作如果父表没有值，则子表拒绝操作。删除动作，子表随父表而执行。

新的外键约束已经创建完成，我们来测试一下。
首先在父表没有数据的情况下向子表内插入一条记录：

mysql> insert into students(name,g_id) values('lisi',2);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demo`.`students`, CONSTRAINT `fk_id` FOREIGN KEY (`g_id`) REFERENCES `grades` (`id`) ON DELETE CASCADE)

可以看到报了一个错误，无法插入。接下来我们先在父表写入一条测试数据，然后再在子表插入。

mysql> insert into grades(grade_name) values('一年级');Query OK, 1 row affected (0.00 sec)mysql> select * from grades;+----+------------+| id | grade_name |+----+------------+|  1 | 一年级     |+----+------------+1 row in set (0.00 sec)mysql> insert into students(name,g_id) values('lisi',1);Query OK, 1 row affected (0.00 sec)mysql> select * from students;+----+------+------+| id | name | g_id |+----+------+------+|  2 | lisi |    1 |+----+------+------+1 row in set (0.00 sec)

可以看到 插入成功了。下面我们删除父表中的数据，然后看子表

mysql> delete from grades where id =1;Query OK, 1 row affected (0.00 sec)mysql> select * from grades;Empty set (0.00 sec)mysql> select * from students;Empty set (0.00 sec)

可见，删除联动也没有问题。