mysql的外键约束_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)