Mysql外键约束

　　外键是表与表之间的某种约定关系，由于这个关系的存在，我们能让表之间的数据更加完整，关联性更强。

(1).创建外键约束

　　创建测试环境：

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> use test_db;
Database changed
mysql> create table user_table(id int key,name varchar(20),age int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user_table values('1','zhangsan','18'),('2','lisi','19'),('3','wangwu','20');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user_table;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   18 |
|    2 | lisi     |   19 |
|    3 | wangwu   |   20 |
+------+----------+------+
3 rows in set (0.00 sec)

　　 create table [表名]([字段名] [字段类型] [字段约束],...,[constraint [外键字段名]] foreign key (本表字段名) references [父表表名]([父表字段名]) [ON DELETE CASCADE] [ON UPDATE CASCADE]);

mysql> create table job_table(job_id int,user_id int,job_name varchar(20),foreign key (user_id) references user_table(id) ON DELETE CASCADE ON UPDATE CASCADE);
Query OK, 0 rows affected (0.05 sec)

　　其中，ON DELETE CASCADE和ON UPDATE CASCADE表示父表删除或更新，那么自动删除或更新子表中对应的行。

　　另外，创建外键需要满足以下条件：1.确保父表和字段的存在；2.父表的字段必须是索引，外键的字段默认是普通索引；3.数据库引擎必须是innodb，一般默认是这个；4.外键字段和关联字段的数据类型必须一致。

(2).删除外键约束

　　alter table [表名] drop foreign key [外键字段名];

mysql> show create table job_table\G　　//没有写外键字段名需要先查看一下
*************************** 1. row ***************************
       Table: job_table
Create Table: CREATE TABLE `job_table` (
  `job_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `job_name` varchar(20) DEFAULT NULL,
  KEY `user_id` (`user_id`),
  CONSTRAINT `job_table_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

mysql> show create table job_table\G
*************************** 1. row ***************************
       Table: job_table
Create Table: CREATE TABLE `job_table` (
  `job_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `job_name` varchar(20) DEFAULT NULL,
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

(3).添加外键约束

　　alter table [表名] add [constraint [外键字段名]] foreign key (本表字段名) references [父表表名]([父表字段名]) [ON DELETE CASCADE] [ON UPDATE CASCADE];

mysql> alter table job_table add foreign key (user_id) references user_table(id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table job_table\G                                                                *************************** 1. row ***************************
       Table: job_table
Create Table: CREATE TABLE `job_table` (
  `job_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `job_name` varchar(20) DEFAULT NULL,
  KEY `user_id` (`user_id`),
  CONSTRAINT `job_table_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)