生活现象:
不知你们是否遇到过这样的现象,就是你辛辛苦苦花了几十块钱注册一个会员,结果家里的七大姑,八大姨都要拿去用,而且完全可以用。还有就是一个淘宝账号里却可以添加好多个收获地址(里面包括收货人的姓名,手机号,以及收货地址)
这里面其实都用到了外键!
我们在数据库操作系统里演示一下外键:
第一部分: (未使用外键)
- 先创建一个用户信息表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_info和password,而这两者是注册用户的信息(理论上只需一个就行了,不需要重复创建)
第二部分:(在逻辑上使用了外键)
-
所以为了解决这个问题,就需要引入外键来将两者(注册用户的信息和收货人的信息)分离(先删除上一个user_info表:
drop table user_info
) -
先创建一个用户注册表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');
我们查看一下用户表的内容:select * from user_info
- 再创建一个地址表:
# 这个表中存储收货人的信息,并和用户表有联系
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','山西大同');
此时我们再查看一下地址表中内容:select * from address
- 联系上一个用户表,我们发现此时一个注册用户可以有多个收货人的信息了,不会再重复创建注册用户的信息了。
但我们会发现这样写有致命的缺陷:
第三部分:(实际上使用了外键)
-
上面的写法之所以有缺陷是因为,那只是逻辑上的外键,实际上还是会产生垃圾数据,我们来测试一下:
逻辑上,只要不存在注册用户表,那么地址表(存储收货人的信息的)里的数据是不存在的(无效的),但是当我们删除了用户表:drop table user_info
后,我们来看一下地址表:select * from address
:
发现地址表中的数据仍存在(有效)
或者说向地址表中添加一个用户表中根本不存在的id(即用户根本还没注册),逻辑上是无法向地址表中添加数据的,但实际上却可以:insert into address (id,user_info_id,real_name,mobile,address) values ('bfb9472a-7911-4e6f-a478-3b819454ebab','sdjfljsdjfklsjfjdf','张三','18920120206','河南安阳');
,此时我们再来查看一下地址表:select * from address
:
我们发现已经添加进去了,这就与逻辑相违背了 -
为了杜绝这种现象,我们就要引入真正的外键:
(先删除之前的用户表和地址表:drop table user_info; drop table address;
) -
先创建一个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');
- 再创建一个地址表(用于存放收货人的信息)(与之前的地址表有所不同)
关键是:foreign key (user_info_id) references user_info(id)
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,
foreign key (user_info_id) references user_info(id)
)
其中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
:
这时就可以防止产生无效(垃圾)数据了,即当我们要删除用户表drop table user_info
时,会发现删除不了(因为用户表中的变量id(地址表中的外键)在地址表中已使用),
而且当用户未注册时,也不能向地址表中添加数据:
insert into address (id,user_info_id,real_name,mobile,address)
values ('bfb9472a-7911-4e6f-a479-3b819454ebab','dfdfdfddfdfdf','张三','18920120206','河南安阳');
这就完成了外键的引入,其实最关键的就是两步,首先就是应该将用户注册表(user_info)和地址表(address)分离,再次就是要指出外键及出处。