MySQL 约束

完整性约束

为了保证插入数据的正确性和合法性，给表中字段添加，除了数据类型约束以外的其他约束条件。

完整性约束的分类

• 实体完整性：记录之间不能重复。

  • 主键约束(primary key)：唯一并且不能为空
  • 唯一约束(unique)：唯一可以为空
  • 主键自增(auto_increment)

• 域完整性：数据库表的字段，必须符合某种特定的数据类型或约束。

  • 类型约束：在创建表的时候，已经给每个字段添加类型了
  • 非空约束：not null
  • 默认值：default

• 引用完整性(参照完整性)：一张表中字段的值，需要参考另外一张表中的值。

  • 添加外键约束：foreign key
  • 引用完整性会降低sql的执行效率，有时候能不用就不用。

——————————————————

1、非空约束，not null

MySQL非空约束（NOT NULL） (biancheng.net)

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空。

注意：not null 约束只有列级约束。没有表级约束。

create table t_user (
    no bigint,
    name varchar(255) not null
);

执行以下语句（name字段为空）：
insert into t_user(no) values(1);

报错：
[SQL]insert into t_user(no) values(1);
[Err] 1364 - Field 'name' doesn't have a default value

也可以给 not null 字段添加默认值，这样就不会报错：

create table t_user (
    no bigint,
    name varchar(255) default 'xxx' not null
);

2、唯一约束，unique

MySQL唯一约束（UNIQUE KEY） (biancheng.net)

唯一约束与主键约束相似的是它们都可以确保列的唯一性。

不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。

而主键约束在一个表中只能有一个，且不允许有空值。

比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

2.1、设置编号是唯一的（列级约束：该约束只应用于相关的一列上）：

create table t_user (
    no bigint unique,
    name varchar(255)
);

插入相同的编号：
insert into t_user(no,name) values (1,'小明');
insert into t_user(no,name) values (1,'小龙');

报错：
Error Code: 1062. Duplicate entry '1' for key 't_user.no'

2.2、给两个列或者多个列添加 unique（表级约束：可以应用在一个表中的多列上）：

创建表，设置 unique(no,name)
create table t_user(
    no bigint,
    name varchar(255),
    password varchar(15),
    unique(no,name)
);

执行以下代码：
insert into t_user(no,name) values (1,'小明');
insert into t_user(no,name) values (1,'小龙');
select * from t_user;

运行是没有错误的，上面建表语句结果unique(no,name)只有在no和name同时相同时才会报错。

3、主键约束，primary key

MySQL主键（PRIMARY KEY） (biancheng.net)

添加主键 primary key 的字段即不能重复也不能为空

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，

单一主键是由一个字段构成的；复合（联合）主键是由多个字段构成的。

如果一张表没有定义主键，表中第一个定义了 “not null nuique” 的字段，会自动转换为主键约束。

3.1、单一主键

单一主键：一张表中的一个字段作为主键。

建表，把编号 no 设置为主键：

create table t_user(
        no bigint primary key,
        name varchar(255) unique
);

# 或者使用表级约束方式定义主键：
create table t_user(
        no bigint,
        name varchar(255) unique,
        primary key(no)
);

分别运行下面两段sql，都会报错：
insert into t_user(no,name) values (1,'小明');
insert into t_user(no,name) values (1,'小龙');

insert into t_user(name) values ('小龙');
测试得出：no是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。

3.2、复合主键

drop table if exists t_user;
create table t_user(
        no bigint,
        name varchar(255),
        primary key(no,name)
);

复合主键中的所有值都重复才算重复，这样是允许的：
insert into t_user(no,name) values (1,'小明');
insert into t_user(no,name) values (1,'小龙');

3.3、自然主键和业务主键

自然主键：主键值最好就是一个和业务没有任何关系的自然数。（推荐）

业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐）

最好不要拿和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

3.4、MySQL 提供主键值自增（auto_increment）

建表，no字段从1开始，以1递增：

drop table if exists t_user;
create table t_user(
    no bigint primary key auto_increment,
    name varchar(255)
);
insert into t_user(name) values ('a');
insert into t_user(name) values ('b');
insert into t_user(name) values ('c');
insert into t_user(name) values ('d');
insert into t_user(name) values ('e');
select * from t_user;

4、外键约束，foreign key

MySQL外键约束（FOREIGN KEY） (biancheng.net)

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。

存在外键的表就是子表，参照的表就是父表。

4.1、定义一个外键时，需要遵守下列规则：

父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为父表定义主键。

主键不能包含空值，但允许在外键中出现空值。也就是说，可以不给外键赋值，但赋值就必须是父表的主键中已存在的值。

在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。

外键中列的数目必须和父表的主键中列的数目相同。

外键中列的数据类型必须和父表主键中对应列的数据类型相同。

外键可以为NULL。

建立一个表，area_number 为1代表是中国地区，为0是海外地区：

drop table if exists t_user;
create table t_user(
        no bigint primary key auto_increment,
        name varchar(255),
        area_number int(1),
        area char(4)
);
insert into t_user(name,area,area_number) values('小明','中国地区',1);
insert into t_user(name,area,area_number) values('小龙','中国地区',1);
insert into t_user(name,area,area_number) values('Ana','海外地区',0);
insert into t_user(name,area,area_number) values('Jan','海外地区',0);
insert into t_user(name,area,area_number) values('Fak','海外地区',0);
insert into t_user(name,area,area_number) values('小法','中国地区',1);
select * from t_user;

可以很清楚地看出此表的缺点：数据太冗余！！

解决方法：我们可以借由两张表来维护这个用户信息。t_user（子表）、t_area（父表）

drop table if exists t_user;
drop table if exists t_area;

create table t_area(
        area_number int(1) primary key,
        area char(4)
);
create table t_user(
        no bigint primary key auto_increment,
        name varchar(255),
        a_number int(1),
        foreign key(a_number) references t_area(area_number)
);

insert into t_area values (1,'中国地区'),(0,'海外地区');
insert into t_user(name,a_number) values ('小明',1), ('小龙',1),('Ana',0),('Jan',0),('Fak',0),('小法',1);

select * from t_area;
select * from t_user;

通过两表操作，数据不再冗余。找出用户的信息：

select u.*,a.* from t_user u join t_area a on u.a_number=a.area_number;

4.2、父表和子表的操作顺序

删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，再添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，再删除父表。

5、检查约束 CHECK

MySQL检查约束（CHECK） (biancheng.net)

检查约束是用来检查数据表中，字段值是否有效的一个手段。
例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的。如果是大学生，年龄一般应该在 18~30 岁之间。在设置字段的检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

6、默认值约束 Default

MySQL默认值（DEFAULT） (biancheng.net)

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。
例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。
默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法格式如下：

<字段名> <数据类型> DEFAULT <默认值>;

其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。例如：

创建表时，添加默认值约束

修改表时，添加默认值约束

删除默认值约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> DEFAULT NULL;

文章内容转载自：MySQL 常见约束_知其黑、受其白的博客-CSDN博客_mysql常见约束