MySQL表的增删查改（2）——数据库约束

1. 数据库约束

1.1 约束类型

NOT NULL：指定某列不能存储null值

UNIQUE：保证某列的每行必须有唯一的值

DEFAULT：规定没有给列赋值时的默认值

PRIMARY KEY：NOT NULL和UNIQUE的结合，确保某列（或两个列或多个列的结合）有唯一标识，有助于更容易快速的找到表中的一个特定的记录（主键）

FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性（外键）

CHECK：保证列中的值符合指定的条件，对于MySQL数据库，对check子句进行分析，但是忽略check子句（即在 MySQL 中 check 指定的条件不生效，若在 SQL server 这种支持 check 的数据库中就会生效）

1.2 NOT NULL约束

创建表时，指定某列不为空：

-- 重新设置学生表结构
drop table if exists student;

-- 指定id列不能为空
create table student (id int not null, name varchar(20));

 该约束可以限制插入与修改：

1.3 UNIQUE：唯一约束

drop table if exists student;

-- 指定id列为唯一的、不重复的
create table student (id int unique, name varchar(20));

 

1.4 DEFAULT：默认值约束

drop table if exists student;

-- 指定插入数据时，name列为空，默认值为 无名
create table student (id int, name varchar(20) default '无名');

 

1.5 PRIMARY KEY：主键约束

drop table if exists student;

-- 指定id列为主键
create table student (id int primary key, name varchar(20));

性质：

虽然数据库不允许在一个表中存在多个主键，但是允许把多个列共同作为一个主键（联合主键）

---

对于整数类型的主键，常搭配自增长 auto_increment 来使用

create table student (id int primary key auto_increment, name varchar(20));

 

插入数据可以不指定id列的值，由数据库自动分配，按照1，2，3，4...

---

也能手动插入指定其他数值：

---

插入数据对应字段不给值时，使用最大值+1

在mysql中，给每个表都记录了一个“自增主键的最大值”，后续继续插入元素，无论之前的最大值是否仍然存在，都是根据之前保存的最大值继续往后分配的

即使将id为100，101的记录删除，插入下一条id列不给值的数据时，id自动赋值为102

自增主键只能针对 int / bigint 这样的整数，也有可能会有使用字符串做主键的，但大部分还是用数字做主键

1.6 FOREIGN KEY：外键约束

外键用于关联其他表的主键或唯一键，语法：

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

示例：

创建班级表class，classId为主键：

drop table if exists class;

create table class (classId int primary key, className varchar(20));

创建学生表student，此次不使用外键：

create table student (id int primary key, name varchar(20), classIn int);

 

---

创建学生表student，一个学生对应一个班级，一个班级对应多个学生，使用id为主键，classId为外键，关联班级表classId

drop table if exists student;

create table student (
    id int primary key, 
    name varchar(20), 
    classId int, 
    foreign key(classId) references class(classId)
);

此时，外键约束就出现了，再次往student中插入数据，就需要确保插入的数据的classId的值，与class表中匹配

 

 tip：如果父表中的某个记录被子表引用了，此时就不能删除/修改父表中的已被引用的记录了

---

父表约束了子表，子表也会对父表反向进行约束

 进行删除操作时，一定要先删除子表，再删除父表：

---

再次创建班级表和学生表，此时不给班级表设置主键，创建学生表就会失败：

这是因为指定外键后，后续往子表中插入数据，就需要查询父表

父表中主键本身也是有“唯一性要求的”，频繁触发查询，此处数据库就会对主键约束，针对主键列创建索引，加快查询速度

如果不指定主键，加一个unique，也能达成类型的效果，都是要自动建立索引，以便于后续的快速查询：

---

更复杂的场景：

商家一开始卖铅笔，后来改卖圆珠笔：

 

解决方案：逻辑删除

 

1.7 CHECK约束

MySQL使用时不报错，但忽略该约束：

drop table if exists test_user;

create table test_user ( 
    id int,name varchar(20), 
    sex varchar(1), 
    check (sex = '男' or sex = '女') 
);

MySQL数据库，对check子句进行分析，但是忽略check子句，不生效