mysql外键

外键好处：

可以使得两张表关联，保证数据的一致性和实现一些级联操作

外键的使用条件：

1,两张表必须是innoDB存储引擎的表

2,mysql 4.1.2版本之前的外键列需要建立索引

3,外键关系的两个表的列必须数据类型相似

  int tinyint  但是int  varchar就不行

create table tem(id int, name char(20), foreign key(id) references outTable(id) on delete cascade on update cascade);

指定外键字段    foreign key（列名）

参考外表        references <外键表名>（外键列名）

事件触发限制    on delete 和 on update ，可设参数cascade（跟随外表中的外键 改动），restrict(限制外表中的外键改动)  NO ACTION（默认的，无动作）

constraint  约束名称

注意：1、外键表中依附的主键表中没有的内容，外键表中是不能添加的

2、删除的时候要先删除外键表的数据，才能删除外键表依附的主键表的内容

如果加了监听cascade：

1、外键表中依附的主键表中没有的内容，外键表中是不能添加的

2、加了监听这个就能外键表依附的主键表就能删除并且外键表跟着外键表依附的主键表删除

3、外键表删除或者修改，外键表依附的主键表不改变

mysql> create table dage(id int primary key,name varchar(9))engine=innodb defaul
t charset=utf8;
Query OK, 0 rows affected (0.26 sec)

mysql> insert into dage values('1','dg1');
Query OK, 1 row affected (0.05 sec)

mysql> create table xiaodi(id int primary key auto_increment,foreign_id int,name
 varchar(9),constraint xd foreign key(foreign_id) references dage(id))engine=inn
odb default charset=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into xiaodi values('1','2','xd1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`lianxi/xiaodi`, CONSTRAINT `xd` FOREIGN KEY (`foreign_id`) REFERENCES `da
ge` (`id`))
mysql> select * from dage;//外键表中依附的主键表中没有的内容，外键表中是不能添加的
+----+------+
| id | name |
+----+------+
|  1 | dg1  |
+----+------+
1 row in set (0.00 sec)

mysql> insert into xiaodi values('1','1','xd1');
Query OK, 1 row affected (0.06 sec)

mysql> select * from xiaodi;
+----+------------+------+
| id | foreign_id | name |
+----+------------+------+
|  1 |          1 | xd1  |
+----+------------+------+
1 row in set (0.00 sec)

mysql> delete from dage where id=1;//删除的时候要先删除外键表的数据，才能删除外键表依附的主键表的内容
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`lianxi/xiaodi`, CONSTRAINT `xd` FOREIGN KEY (`foreign_id`) REFERENCES
 `dage` (`id`))
mysql> delete from xiaodi where id=1;
Query OK, 1 row affected (0.06 sec)

mysql> delete from dage where id=1;
Query OK, 1 row affected (0.06 sec)

//2222222222222

mysql> drop table xiaodi;
Query OK, 0 rows affected (0.50 sec)

mysql> create table xiaodi(id int primary key auto_increment,foreign_id int,name
 varchar(9),constraint xd foreign key(foreign_id) references dage(id) on delete
cascade on update cascade)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.21 sec)

mysql> insert into xiaodi values('4','3','xiaodi4');
Query OK, 1 row affected (0.13 sec)

mysql> insert into xiaodi values('5','5','xiaodi5');//外键表中依附的主键表中没有的内容，外键表中是不能添加的
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`lianxi/xiaodi`, CONSTRAINT `xd` FOREIGN KEY (`foreign_id`) REFERENCES `da
ge` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> insert into dage values('5','dage5');
Query OK, 1 row affected (0.06 sec)

mysql> insert into xiaodi values('5','5','xiaodi5');
Query OK, 1 row affected (0.06 sec)

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  2 | dage1 |
|  3 | dg2   |
|  5 | dage5 |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from xiaodi;
+----+------------+---------+
| id | foreign_id | name    |
+----+------------+---------+
|  4 |          3 | xiaodi4 |
|  5 |          5 | xiaodi5 |
+----+------------+---------+
2 rows in set (0.00 sec)

mysql> delete from dage where id=5;//加了监听这个就能外键表依附的主键表就能删除
Query OK, 1 row affected (0.05 sec)

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  2 | dage1 |
|  3 | dg2   |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from xiaodi;//外键表跟着外键表依附的主键表删除
+----+------------+---------+
| id | foreign_id | name    |
+----+------------+---------+
|  4 |          3 | xiaodi4 |
+----+------------+---------+
1 row in set (0.00 sec)

mysql> update dage set id=7 where id=3;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  2 | dage1 |
|  7 | dg2   |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from xiaodi;
+----+------------+---------+
| id | foreign_id | name    |
+----+------------+---------+
|  4 |          7 | xiaodi4 |
+----+------------+---------+
1 row in set (0.00 sec)

mysql> delete from xiaodi where id=4;//外键表删除或者修改，外键表依附的主键表不改变
Query OK, 1 row affected (0.06 sec)

mysql> select * from xiaodi;
Empty set (0.00 sec)

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  2 | dage1 |
|  7 | dg2   |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into xiaodi values('5','2','kk');
Query OK, 1 row affected (0.12 sec)

mysql> insert into xiaodi values('6','7','kk');
Query OK, 1 row affected (0.12 sec)

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  2 | dage1 |
|  7 | dg2   |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from xiaodi;
+----+------------+------+
| id | foreign_id | name |
+----+------------+------+
|  5 |          2 | kk   |
|  6 |          7 | kk   |
+----+------------+------+
2 rows in set (0.00 sec)

mysql> update xiaodi set foreign_id=2 where id=6;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dage;
+----+-------+
| id | name  |
+----+-------+
|  2 | dage1 |
|  7 | dg2   |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from xiaodi;
+----+------------+------+
| id | foreign_id | name |
+----+------------+------+
|  5 |          2 | kk   |
|  6 |          2 | kk   |
+----+------------+------+
2 rows in set (0.00 sec)