MySQL外键+唯一索引
2019-08-22
1.外键
是指一个表里的列与另一张表里的列进行关系连接，可用于使用简单的数字或字母替代复杂的数据结构，不仅可以节省空间，也拥有约束功能，可减少书写出错的机会。
1.1使用方式
constraint 变量名 foreign key (列名) references 表名2(列名2)；其中变量名是自己给定的，约定不可以有重复；
列名即需要连接外键的列；
表名2值指的是外键的表；
列名2是和表中连接对应的列；
1.2实例
例如下面两个表，表1是机器状态表，表2是机器信息表；将机器的四个状态通过1，2，3，4这4个值表示出来：
create table machinestatus(
status_id int auto_increment primary key,
status_name char(20)
)engine=innodb default charset=utf8;
insert into machinestatus(status_name) values("待机"),("运行"),("故障"),("关机");
create table machineinfo(
id int auto_increment primary key,
identifier int not null,
status int,
constraint fk_info_status foreign key (status) references machinestatus(status_id)
)engine=innodb default charset=utf8;即将machineinfo表中的status与machinestatus中的id对应起来。
2.唯一索引
唯一索引使用关键字：unique；具有以下特点
- 不可重复
- 加速查找
- 可以为空
- 可以由多列联合生成
唯一索引与主键的区别是：1）唯一索引中可以有数据为空，2）唯一索引在一个表中可以不止一个。
2.1使用方式：
unique 变量名 (列1，列2···)如果是单列的唯一索引，括号内填写一个列名即可。
2.2实例
单列唯一索引
create table userinfo(
id int auto_increment primary key,
age int,
name char(10),
card int not null,
email char(20),
unique ucard (card)
)engine=innodb default charset=utf8;联合唯一索引
create table userinfo(
id int auto_increment primary key,
age int,
name char(10),
card int not null,
phone_num char(20),
email char(20),
unique ucard (card,phone_num)
)engine=innodb default charset=utf8;3.外键+唯一索引
3.1 外键实现一对多引用
外键表中被连接的数据可以多次重复出现在使用其的表中；例如
--部门表
create table department(
id int not null auto_increment primary key,
depart char(64)
)engine=innodb default charset=utf8;
--员工信息表
create table userinfo3(
id int not null auto_increment primary key,
name char(20) not null,
age int,
depart_id int not null,
constraint fk_u2d_depart foreign key (depart_id) references department(id)
)engine=innodb default charset=utf8;部门表中的部门id中的个值可以多次重复的被员工信息表引用。
3.2 外键约束+唯一索引约束实现一对一引用
--用户表
create table userinfo(
id int not null auto_increment primary key,
name char(20) not null,
)engine=innodb default charset=utf8;
--旅行登记表
create table travelregister(
id int not null auto_increment primary key,
job_num int,
constraint fk_t2u_depart foreign key (job_num) references userinfo(id),
unique uj (job_num)
)engine=innodb default charset=utf8;旅行登记表中登记的是参加旅行的员工，每个员工最多登记一次，通过外键引用员工表的id来表示员工本人，而通过unique来限制员工重复出现；使员工表id个值最多被引用一次。
3.3 外键约束实现多对多引用
可以重复记录
--员工表
create table userinfo2(
id int auto_increment primary key,
name char(10) not null,
gender char(10),
)engine=innodb default charset=utf8;
--主机表
create table host(
id int auto_increment primary key,
hostname char(64) not null
)engine=innodb default charset=utf8;
--员工主机关系表
create table user2host(
id int auto_increment primary key,
user_id int not null,
host_id int not null,
constraint fk_u2h_user foreign key (user_id) references userinfo2(id),
constraint fk_u2h_host foreign key (host_id) references host(id)
)engine=innodb default charset=utf8;3.4外键约束+唯一索引约束实现多对多引用
可以防止重复记录
--员工表
create table userinfo2(
id int auto_increment primary key,
name char(10) not null,
gender char(10),
)engine=innodb default charset=utf8;
--主机表
create table host(
id int auto_increment primary key,
hostname char(64) not null
)engine=innodb default charset=utf8;
--员工主机关系表
create table user2host(
id int auto_increment primary key,
user_id int not null,
host_id int not null,
constraint fk_u2h_user foreign key (user_id) references userinfo2(id),
constraint fk_u2h_host foreign key (host_id) references host(id),
unique uq_user_host (user_id,host_id)
)engine=innodb default charset=utf8;
