一文带你了解MySQL之约束

在SQL标准中，一共规定了6种不同的约束，

包括非空约束，唯一约束和检查约束等，而在MySQL中是不支持检查约束的，所以这篇文章先对其余5种约束做一个详解和练习。

文章目录

1. 约束的概念

约束是作用于表中列上的规则，用于限制加入表的数据。

例如，作为主键的列一定是非空的唯一的，否则将无法对数据进行区分。约束的存在保证了数据库中数据的正确性、有效性和完整性。所以约束在数据库设计中是非常重要的。

2. 约束的分类

前面说到SQL标准把约束分为了6大类，分别是非空约束，唯一约束，主键约束，检查约束，默认约束和外键约束，添加约束时我们只需要在SQL中添加关键词，便可以限制表中的数据。
约束类型功能非空约束 NOT NULL保证列中所有的数据不能有null值唯一约束 UNIQUE保证列中所有数据各不相同主键约束 PRIMARY KEY主键是一行数据的唯一标识，要求非空且唯一检查约束 CHECK保证列中的值满足某一条件默认约束 DEFAULT保存数据时，未指定值则采用默认值外键约束 FOREIGN KEY外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

3. 非空约束

目的：保证列中所有的数据不能有null值

添加约束：

CREATETABLE 表名(
    列名 数据类型 NOTNULL,
    …
);

建完表后添加非空约束：

ALTERTABLE 表名 MODIFY 字段名 数据类型 NOTNULL;

删除约束：

ALTERTABLE 表名 MODIFY 字段名 数据类型;

4. 唯一约束

目的：保证列中所有数据各不相同

添加约束：

CREATETABLE 表名(
    列名 数据类型 UNIQUE[AUTO_INCREMENT],-- AUTO_INCREMENT: 当不指定值时自动增长
    …
);CREATETABLE 表名(
    列名 数据类型,
    …
    [CONSTRAINT][约束名称]UNIQUE(列名));

建完表后添加唯有约束：

ALTERTABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

删除约束：

ALTERTABLE 表名 DROPINDEX 字段名;

5. 主键约束

目的：主键是一行数据的唯一标识，要求非空且唯一

添加约束：

CREATETABLE 表名(
    列名 数据类型 PRIMARYKEY[AUTO_INCREMENT],
    …
);CREATETABLE 表名(
    列名 数据类型,[CONSTRAINT][约束名称]PRIMARYKEY(列名));

建完表后添加主键约束：

ALTERTABLE 表名 ADDPRIMARYKEY(字段名);

删除约束：

ALTERTABLE 表名 DROPPRIMARYKEY;

6. 默认约束

目的：保存数据时，未指定值则采用默认值

添加约束：

CREATETABLE 表名(
    列名 数据类型 DEFAULT 默认值,
    …
);

建完表后添加默认约束：

ALTERTABLE 表名 ALTER 列名 SETDEFAULT 默认值;

删除约束：

ALTERTABLE 表名 ALTER 列名 DROPDEFAULT;

7. 外键约束

当我们添加了外键以后，就在数据库层面建立了两张表的关系。

目的：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

添加约束：

CREATETABLE 表名(
    列名 数据类型,
    …
    [CONSTRAINT][外键名称]FOREIGNKEY(外键列名)REFERENCES 主表(主表列名));

建完表后添加外键约束：

ALTERTABLE 表名 ADDCONSTRAINT 外键名称 FOREIGNKEY(外键字段名称)REFERENCES 主表名称(主表列名称);

删除约束：

ALTERTABLE 表名 DROPFOREIGNKEY 外键名称;

7. 约束的案例练习

首先，我们使用以下的案例来练习约束：

-- 删除stu表droptableifexists stu;-- 创建stu表CREATETABLE stu (
id intprimarykey,-- 编号 主键
name varchar(10)notnullunique,-- 姓名 非空，唯一
age intnotnull,-- 年龄 非空
gender varchar(5)notnull,-- 性别 非空
math double(5,2)notnull,-- 数学成绩  非空
english double(5,2)default0-- 英语成绩 默认为0);

验证主键约束，其特点是非空且唯一，先添加一条数据：

insertinto stu(id,name,age,gender,math,english)values(1,'小张',23,'男',66,78);

添加第二条数据时，尝试 id 添加为空值：

insertinto stu(id,name,age,gender,math,english)values(null,'小李',20,'女',98,87);

尝试 id 添加为重复的值：

insertinto stu(id,name,age,gender,math,english)values(1,'小陈',55,'男',56,77);

我们已经验证了主键约束，当我们添加不合法的数据时，添加失败。

验证非空约束：

当我们添加以下的数据时：

insertinto stu(id,name,age,gender,math,english)values(2,NULL,20,'女',76,65);

验证唯一约束：

当我们添加以下的数据时：

insertinto stu(id,name,age,gender,math,english)values(5,'小张',20,'男',86,NULL);

验证默认约束：

当我们添加以下的数据时：

insertinto stu(id,name,age,gender,math)values(6,'小赵',23,'男',99);

验证外键约束：

我们使用一下的案例来验证外键约束：

-- 删除表DROPTABLEIFEXISTS emp;DROPTABLEIFEXISTS dept;-- 部门表CREATETABLE dept(
id intprimarykeyauto_increment,
dep_name varchar(20),
addr varchar(20));-- 员工表CREATETABLE emp(
id intprimarykeyauto_increment,
name varchar(20),
age int,
dep_id int,-- 添加外键 dep_id,关联 dept 表的id主键CONSTRAINT fk_emp_dept FOREIGNKEY(dep_id)REFERENCES dept(id));

此时，我们在员工表中添加了外键，相当于在数据库层面上建立了两张表的关系，此时如果员工表中有员工 a ，他属于 1 号部门，那么我们想要删除部门表中的 1 号部门就会删除失败，因为员工 a 是属于 1 号部门的，此时两张表建立了关系。

添加数据：

-- 添加 2 个部门insertinto dept(dep_name,addr)values('研发部','西安'),('销售部','成都');-- 添加员工,dep_id 表示员工所在的部门INSERTINTO emp (NAME, age, dep_id)VALUES('张三',20,1),('李四',20,2);

此时想要是删除销售部门时，发现删除失败。