数据库初学之为什么需要外键

（注：本文使用的是Navicat Premium可视化数据库管理工具来操作数据库）

思考

如何将亚马逊中Sommerfeld的用户信息及其多个邮寄商品地址保存到数据库中?

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','Sommerfeld','123456','泡利','18920120206','奥地利 维也纳');
insert into user_info (id,user_name,password,real_name,mobile,address) 
values ('cc95772b-75a2-4702-bd99-4c3b0322d606','Sommerfeld','123456','海森堡','18617297545','德国 慕尼黑');
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','Sommerfeld','123456','劳厄','17694976949','德国 普法芬多夫');

 

 输出结果如下

 问题：这种表结构存在严重的字段冗余（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','Sommerfeld','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 user_info (id,user_name,password,real_name,mobile,address) 
values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','Sommerfeld','123456','泡利','18920120206','奥地利 维也纳');
insert into user_info (id,user_name,password,real_name,mobile,address) 
values ('cc95772b-75a2-4702-bd99-4c3b0322d606','Sommerfeld','123456','海森堡','18617297545','德国 慕尼黑');
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','Sommerfeld','123456','劳厄','17694976949','德国 普法芬多夫');

 这样的话显然，重复冗余的内容被我们单独摘出来，放进一个表里面。

但是，这样的话只是一个表之中用户的id被作为另一个表的某个属性，其中在系统上并不存在逻辑关系！

即

这种表结构消除了字段冗余，但由于只是逻辑上的“外键”关系，所以依然无法保证数据完整性，例如可以将user_info中id为51b28fe1-4ebf-41ac-a17b-d5e276861fd0的数据删除，但此时地址表中数据将不再完整——找不到这些地址属于哪个用户，即脏数据；

再例如也可以向address表中添加一条user_info_id不存在的地址信息（如：insert into address (id,user_info_id,real_name,mobile,address) values ('7da42cc6-36a6-4ad5-9998-71dbc30c8e17','ddc376dd-f8b3-42a6-b42a-db22abed1941','鲍林','18338970095','德国慕尼黑');）——同样，该条数据并不完整，依然找不到这些地址属于哪个用户。

方案3

最终改进——外键的使用

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','Sommerfeld','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 user_info (id,user_name,password,real_name,mobile,address) 
values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','Sommerfeld','123456','泡利','18920120206','奥地利 维也纳');
insert into user_info (id,user_name,password,real_name,mobile,address) 
values ('cc95772b-75a2-4702-bd99-4c3b0322d606','Sommerfeld','123456','海森堡','18617297545','德国 慕尼黑');
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','Sommerfeld','123456','劳厄','17694976949','德国 普法芬多夫');

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

（题外话：本文中出现的Sommerfeld就是当年量子力学早期的奠基人索末菲大师，后面出现的泡利、海森堡、劳厄、鲍林都是他的学生并获得过诺贝尔奖，其中泡利海森堡这种天纵之才更是绝非一个诺奖级人才就可以简单概括的。）

他们都是人类心智历史上最为闪耀的群星。