外键的作用

外键（foreign key）的作用是保护数据的完整性。

以下以京东账户注册为例具体说明。

1、

drop table user_info;
create table user_info(
  id char(36) primary key,
  user_name varchar(30) not null,
  password varchar(30) not null,
  real_name varchar(8),
  mobile char(11),
  address varchar(150)
);
insert into user_info (id,user_name,password,real_name,mobile,address) 
values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456','张三','18920120206','河南安阳');
insert into user_info (id,user_name,password,real_name,mobile,address) 
values ('cc95772b-75a2-4702-bd99-4c3b0322d606','fuliuqingfeng','123456','李四','18617297545','北京海淀');
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','fuliuqingfeng','123456','王五','17694976949','山西大同');
select * from user_info

该表的user_name列和password列存在数据冗余，且数据越多冗余现象越严重。

2、

drop table user_info;
create table user_info(
  id char(36) primary key,
  user_name varchar(30) not null,
  password varchar(30) not null
);
insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456');
create table address(
  id char(36) primary key,
  user_info_id char(36), #user_info_id 指定该地址属于哪个用户
  real_name varchar(8) not null,
  mobile char(11) not null,
  address varchar(150) not null
)
insert into address (id,user_info_id,real_name,mobile,address) 
values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','王五','17694976949','山西大同');
select * from address

以上两表虽然减少了数据冗余，但会导致数据不完整，如下两个例子：
（1）address表中的数据不能再找到user_info中的数据，address表中的数据成为垃圾数据
delete from user_info where id = '51b28fe1-4ebf-41ac-a17b-d5e276861fd0' 
（2）address表中新添加的user_info_id在user_info表中并不存在，新添加的数据成为垃圾数据
insert into address (id,user_info_id,real_name,mobile,address) values ('bfb9472a-7911-4e6f-a479-3b719454ebab','shaknfjnnnjskskkskkskkkss','张三','18920120206','河南安阳'); 

3、为解决这个问题，我们需要外键
为user_info_id添加了外键，指向user_info表的主键，作用是保护数据的完整性：
如果删除的用户信息id已经在address表中使用，则该条数据无法删除；无法向address表中添加用户id不存在的地址信息。 

drop table user_info;
create table user_info(
  id char(36) primary key,
  user_name varchar(30) not null,
  password varchar(30) not null
);
insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456');
drop table address;
create table address(
  id char(36) primary key,
  user_info_id char(36), #user_info_id 指定该地址属于哪个用户
  real_name varchar(8) not null,
  mobile char(11) not null,
  address varchar(150) not null,
	foreign key(user_info_id) references user_info(id)#外键
);
insert into address (id,user_info_id,real_name,mobile,address) 
values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','王五','17694976949','山西大同');
select * from address

delete from user_info where id = '51b28fe1-4ebf-41ac-a17b-d5e276861fd0' #失败
insert into address (id,user_info_id,real_name,mobile,address) 
values ('bfb9472a-7911-4e6f-a479-3b719454ebab','shaknfjnnnjskskkskkskkkss','张三','18920120206','河南安阳') #失败