数据库——外键的作用

我们用一个比较实际的例子来描述问题，并讲解外键作用：

• 当我们在网上购物的时候，我们有我们的帐号，还有我们所在不同地方的地址信息如公司，学校，家里，或者给亲戚朋友送个礼物，收货人信息也不一样。

我们就一步一步讨论以上提到的这些信息如何更合理的存储在数据库中

• 第一步、我们把所用信息都存入到一个表中：

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','qiangzhogshou','123456','小明','12345678912','物理课');
insert into user_info (id,user_name,password,real_name,mobile,address) 
values ('cc95772b-75a2-4702-bd99-4c3b0322d606','qiangzhogshou','123456','李华','12345678934','英语课');
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','qiangzhogshou','123456','文言文','12345678956','语文课');

我们发现如果在同一个表中，同一个账户的地址信息每增加一行都要多重复一行该账户的注册信息
问题：这种表结构存在严重的字段冗余（user_name和password列），如果个人信息字段比较多这一问题表现的越严重。

• 第二步、把这些信息存入到两个表中（把账户信息，地址信息分开存储）

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','qiangzhogshou','123456');

create table address(
  id char(36) primary key,
  user_info_id char(36),
  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','小明','12345678912','物理课');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李华','12345678934','英语课');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','文言文','12345678956','语文课');

• 如下图为user_info表
  
• 如下图为address表
  
  我们会发现，这样就基本解决了字段冗余的问题，帐户表中每个账户有不同的id，而address表中，用于对应账户信息的，只有一条账户id。

但是：
①我们只是在address表里面写了一个新字段，内容是账户id，这只是在逻辑上把两个表联系起来，我们看到两个表的时候，比对了就知道
而数据库系统并没有建立起来两个表之间的联系，它只知道有两个表，不管内容一不一样，多么有逻辑，如果在建表的时候，没有建立两个表字段上逻辑关系的物理连接，数据库就只知道这是两个分开的表。
②这就导致，在对表执行操作的时候，无法捆绑另一个表信息的同步：比如删除了user_info中的数据，那么逻辑上应该地址也没有用了，但是因为是两个独立的表，address中的数据不会受影响。又或者在address中添加了一个随意的user_info_id的数据，但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','qiangzhogshou','123456');

create table address(
  id char(36) primary key,
  user_info_id char(36),
  real_name varchar(8) not null,
  mobile char(11) not null,
  address varchar(150) not null,
  constraint address_user_info_id_fk 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','小明','12345678912','物理课');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李华','12345678934','英语课');
insert into address (id,user_info_id,real_name,mobile,address) 
values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','文言文','12345678956','语文课');

• 如下图为user_info表
  
• 如下图为address表
  
  我们发现：
  ①使用外键前后，两个表看起来没有区别（这也可以说明：第二步中，只是逻辑通）
  ②外键的使用语句：
  constraint address_user_info_id_fk foreign key(user_info_id) references user_info(id)

结果：这种方案为user_info_id添加了外键，指向user_info表的主键，该约束起到了保护数据完整性的作用：如果删除的用户信息id已经在address表中使用，则该条数据无法删除；无法向address表中添加用户id不存在的地址信息。

总结：

外键的作用：
①解决了数据冗余问题
②实现两个表数据库内的的物理连接
③避免了脏数据的产生