用MySQL数据库在表中存储数据时,如果要存储类似京东、淘宝等购物网站的购物信息,一个账户是可以设置多个收货人、收货地址的,那么这时在表中存储的数据应该是这样的:
create table user_info(
id char(36) primary key comment 'id标识列',
user_name varchar(30) not null comment '用户名',
password varchar(30) not null comment '密码',
real_name varchar(8) comment '收件人',
mobile char(11) comment '收件人电话',
address varchar(150) comment '收件人地址'
);
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;
但是这种存储方式如果用户信息有多个字段,则需要将所有用户信息重复存储多遍,这便造成了存储空间的浪费,所以为了解决这个问题,我们将存储方式改进,再创建一个表存储收件人的信息,重复的用户信息只需要在第一个表中存储一次便可以了:
create table user_info(
id char(36) primary key comment 'id标识列',
user_name varchar(30) not null comment '用户名',
password varchar(30) not null comment '密码'
)
insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456');
create table address(
id char(36) primary key comment 'id标识列',
user_info_id char(36) comment '标识user_info表中用户',
real_name varchar(8) not null comment '收件人',
mobile char(11) not null comment '收件人电话',
address varchar(150) not null comment '收件人地址'
)
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 user_info;
select * from address;
我们可以看到,使用这种方式,只需要将用户信息的id重复存储在address表中的user_inifo_id中即可,不用将用户信息重复多遍了。
但是这种方式还是存在问题,如果将user_info表中的这条用户信息删除,那么address表中的这三条数据就变成了垃圾数据,因为并没有用户与之对应,而且即使user_info_id传入一个user_info中没有的id,还是可以传入成功的,也就是说,这两个表只存在逻辑上的联系,并不存在物理上的联系,在这两个表中都可以对数据进行独立的操作,垃圾数据便这样产生了,为了建立这两个表在物理上的联系,于是我们使用外键:
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','山西大同');
select * from user_info;
select * from address;
这时假如我们要删除user_info表中的该条用户信息,使address表中的数据变成垃圾数据,会发现删除不掉,这就是外键的作用:
而且,向address表中添加的数据如果user_info_id是user_info表中没有的id的话,也是不能添加成功的: