mysql外键

定义

foreign key就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整，关连性更强
表A的主键字段是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表
外键是用来实现参照完整性的，外键约束方式将可以使两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。外键主要用来保证数据的引用完整性和一致性

创建外键

外键： 每次插入或更新时，都会检查数据的完整性

create table 表名(…,[CONSTRAINT [外键名]] FOREIGN KEY [字段名,字段名2……] REFERENCES 主表名 [ON DELETE CASCADE |RESTRICT] [ON UPDATE CASCADE|RESTRICT]);
##constraint 外键起名
##foreign key 指定外键字段
##references 指定主表参考字段
##on delete cascade 级联删除
##on update cascade 级联更新
##restrict表示拒绝对父表的更新或删除工作

创建外键需要满足：
1、确保参照的表和字段存在。
2、组成外键的字段被索引。
3、必须使用ENGINE指定存储引擎为：innodb.
4、外键字段和关联字段，数据类型必须一致。

mysql> create table `user`(id int auto_increment primary key,name varchar(18) not null default '',sex enum("w","m") default 'w') engine=innodb;
Query OK, 0 rows affected (0.01 sec)
##创建主表`user`

mysql> create table `order`(o_id int auto_increment primary key,u_id int default '0',username varchar(18),money int,index(u_id),constraint order_f_key foreign key(u_id) references user(id) on delete cascade on update cascade) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
##创建从表`order`

mysql> insert into `user`(name,sex)values('HA','m'),('LB','w'),('HPC','m');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into `order` (u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from `order`;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
|    1 |    1 | HA       |   234 |
|    2 |    2 | LB       |   146 |
|    3 |    3 | HPC      |   256 |
+------+------+----------+-------+
3 rows in set (0.00 sec)

mysql> select * from `user`;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | HA   | m   |
|  2 | LB   | w   |
|  3 | HPC  | m   |
+----+------+-----+
3 rows in set (0.00 sec)

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

mysql> select * from `user`;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  2 | LB   | w   |
|  3 | HPC  | m   |
+----+------+-----+
2 rows in set (0.00 sec)

mysql> select * from `order`;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
|    2 |    2 | LB       |   146 |
|    3 |    3 | HPC      |   256 |
+------+------+----------+-------+
2 rows in set (0.00 sec)
##测试级联删除

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

mysql> select * from `user`;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  3 | HPC  | m   |
|  6 | LB   | w   |
+----+------+-----+
2 rows in set (0.00 sec)

mysql> select * from `order`;
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
|    2 |    6 | LB       |   146 |
|    3 |    3 | HPC      |   256 |
+------+------+----------+-------+
2 rows in set (0.00 sec)
##测试级联更新

mysql> insert into `order`(u_id,username,money)values(7,'Find',345);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`haha`.`order`, CONSTRAINT `order_f_key` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
##报错是因为外键约束，order表受user表的约束，在order里面插入一条数据u_id为7用户，在user表里面根本没有，所以插入不进去

mysql> insert into `user` values(7,"Find","m");
Query OK, 1 row affected (0.00 sec)

mysql> insert into `order`(u_id,username,money)values(7,'Find',345);
Query OK, 1 row affected (0.00 sec)
##验证数据完整性

通过alter table创建外键和级联更新、级联删除

alter table 数据表名称 add [constraint [约束名称] ] foreign key (外键字段,…) references 数据表(参照字段,…) [on update cascade] [on delete cascade]，engine=innodb;

mysql> create table order1(o_id int auto_increment primary key,u_id int default '0',username varchar(50),money int,index(u_id)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table order1 add constraint `bk` foreign key(u_id) references user(id) on delete cascade on update cascade,engine=innodb;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除外键

alter table 表名 drop foreign key 外键名

mysql> show create table order1\G
*************************** 1. row ***************************
       Table: order1
Create Table: CREATE TABLE `order1` (
  `o_id` int(11) NOT NULL AUTO_INCREMENT,
  `u_id` int(11) DEFAULT '0',
  `username` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  PRIMARY KEY (`o_id`),
  KEY `u_id` (`u_id`),
  CONSTRAINT `bk` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`) O
N DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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