【MySQL进阶之路】表的约束——主键，自增长，唯一键，外键

目录

主键

复合主键

自增长

唯一键 unique

外键

方案一

方案二

方案三

---

个人主页：东洛的克莱斯韦克-CSDN博客

主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空（必须有非空约束），一张表中最多只能有一个 主键；主键所在的列通常是整数类型。

实例

create table t(
 id int not null primary key comment '学生id',
 name varchar(10) not null comment '学生姓名'
);

主键约束：主键对应的字段中不能重复，一旦重复，操作失败。如下操作

insert into t values(1, '张三');

insert into t values(1, '李四');

当表创建好以后但是没有主键的时候，可以再次追加主键，追加主键的字段必须有非空约束，否则会报错。

alter table 表名 add primary key(字段列表)

删除主键

alter table 表名 drop primary key;

复合主键

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段 作为主键，可以使用复合主键。

create table tt14(
id int unsigned,
course char(10) comment '课程代码',
score tinyint unsigned default 60 comment '成绩',
primary key(id, course) -- id和course为复合主键
);

自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值 +1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:

任何一个字段要做自增长，前提是本身是一个主键

自增长字段必须是整数

一张表最多只能有一个自增长

实例

create table t2
( 
id int primary key auto_increment,
 name varchar(10) not null 
);

只插入name字段的值

insert into t2(name) values('a');
insert into t2(name) values('b');
insert into t2(name) values('c');

结果如下

查看表详细信息

show create table t2 \G

其中AUTO_INCREMENT的值就是下一次要填充的值

在插入后获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

select last_insert_id();

唯一键 unique

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键,

唯一键 unique 就可以 解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

关于唯一键和主键的区别： 我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息 出现重复。

主键和唯一键并不是对立的，而是相辅相成的。

一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对 主键做过大的调整。

场景

学生信息：id  年龄  性别  身份证号  手机号  家庭住址

约束条件

id：可以设置成主键，用于快速索引，自增

年龄：非空约束

性别：非空约束，默认值 '未知'

身份证号：唯一键，非空约束，身份证号一定时唯一的

手机号：非空约束

家庭住址：非空约束，如果不精确到门牌号的话就不用设为唯一键

create table student(
    id tinyint unsigned primary key auto_increment comment '用于表示学生的唯一性',
    age tinyint unsigned not null comment '学生的年龄',
    gender char(2) not null default '未知' comment '学生的性别',
    identity_card varchar(20) not null unique comment '学生的身份证号',
    phone char(11) not null comment '手机号',
    address varchar(50) not null comment '家庭住址'
    );

外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique 约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

用法

foreign key (字段名) references 主表(列)

场景

学生信息：学生id ，姓名 ，班级id

班级信息：班级id ， 班级名称，人数。

方案一

如果我们把班级信息和学生信息揉到一张表中，班级名称字段和人数字段会大量重复。

方案二

那么分别用两张表存储，不加外键约束。这样虽然我们认为这两张表是有关系的，但MySQL服务并不认为这两张表有关系。换句话说，MySQL服务不会拦截一些在逻辑上不和发的操作。

create table class( 
id tinyint unsigned not null, 
name varchar(40) not null ,
 size tinyint unsigned  );

create table student(
    id tinyint unsigned not null, 
    name varchar(40) not null ,
    class_id tinyint unsigned not null
    );

表结构如下

班级表和学生表之间没有加外键约束。

先向班级表插入数据

insert into class values(1,'软件工程1班', 45),(2, '软件工程2班', 50);

结果如下

这表明此时该学校只有这两个班级

再向学生表中插入数据

insert into student values(1,'张三', 1),(2, '李四', 1),(3, '王五', 2);

结果如下

如果此时再向学生表插入 赵六，但他的班级id是3，这显然是不合法的，因为班级表中只有id为1 和 2 的两个班级，但由于没有外键的约束，此时依旧可以插入成功。

那如果我要在班级表中删掉id为1的班级依旧是不合法的，因为id为1的班级此时还有学生，但由于没有外键的约束，依旧可以删除成功

此时我们大概可以理解什么是外键约束了，两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。

解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

方案三

那方案三自然是有外键约束的效果咯~

create table class_3( 
id tinyint unsigned primary key, 
name varchar(40) not null ,
 size tinyint unsigned  );

create table student_3(
    id tinyint unsigned not null, 
    name varchar(40) not null ,
    class_id tinyint unsigned not null,
    foreign key (class_id) references class_3(id)
    );

表结构如下

依旧是先向班级表中插入数据再向学生表中插入数据

insert into class_3 values(1,'软件工程1班', 45),(2, '软件工程2班', 50);

insert into student_3 values(1,'张三', 1),(2, '李四', 1),(3, '王五', 2);

如果此时再向学生表插入 赵六，但他的班级id是3。那么能插入成功吗？

明显是失败的，报错如下

 Cannot add or update a child row: a foreign key constraint fails (`db1`.`student_3`, CONSTRAINT `student_3_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_3` (`id`))

那删除班级表id为1的班级呢？

也失败了，报错如下

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`student_3`, CONSTRAINT `student_3_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_3` (`id`))

所以，这就是外键的作用。