为什么需要数据库外键

逃离我推掉我的手 2022-03-28 04:11 271阅读 0赞

外键指定一个字段或字段组合作为一个外键（即外来的主键或唯一键），该外键和另一个表的主键或唯一键建立起一个关系，只能定义为**表****级**约束。MySQL数据库使用外键条件：两个表存储引擎必须是InnoDB，MyISAM暂时不支持外键；外键列必须建立索引，MySQL4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；外键关系的两表的列的数据类型必须相似，必须是可相互转换类型的列，比如int和tinyint可以，int和char则不可以。接下来通过示例演示数据库外键的必要性：如何将京东fuliuqingfeng的用户信息及其多个邮寄商品地址保存到数据库中?

**方案一**：

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','山西大同');

![20190109155207107.png][]

问题：这种表结构存在严重的字段冗余（id、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','fuliuqingfeng','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','张三','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','山西大同');

问题：这种表结构消除了字段冗余，但由于只是逻辑上的“外键”关系，所以依然无法保证数据完整性，例如可以将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','北京东城区');）——同样，该条数据并不完整，依然找不到这些地址属于哪个用户。

**方案三**：

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),
      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','张三','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','山西大同');

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

[20190109155207107.png]: /images/20220328/b836f6dc6a2e4466bb30d64395868070.png