Oracle 与 MySQL 中的约束

约束概述

约束的作用

约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件。约束是在数据表上强制执行的一些数据校验规则，当执行 DML 操作时，数据必须符合这些规则，如果不符合则无法执行。

约束条件可以保证表中数据的完整性，保证数据间的商业逻辑。

约束的类型

约束条件包括：

非空约束(Not Null)，简称 NN
唯一性约束(Unique)，简称 UK
主键约束(Primary Key)，简称 PK
外键约束(Foreign Key)，简称 FK
检查约束(Check)，简称 CK
自增()，即使用序列
默认(default)

Oracle

1. 非空约束

建表时添加非空约束

非空约束用于确保字段值不为空。默认情况下，任何列都允许有空值，但业务逻辑可能会要求某些列不能取空值。当某个字段被设置了非空约束条件，这个字段中必须存在有效值，即：

当执行 INSERT 操作时，必须提供这个列的数据
当执行 UPDATE 操作时，不能给这个列的值设置为 NULL

建表时添加非空约束：

CREATE TABLE employees (
    eid NUMBER(6),
    name VARCHAR2(30) NOT NULL,
    salary NUMBER(7, 2),
    hiredate DATE  CONSTRAINT employees_hiredate_nn NOT NULL
);

修改表时添加非空约束

可以在建表之后，通过修改表的定义，添加非空约束：

ALTER TABLE employees MODIFY (eid NUMBER(6) NOT NULL);

取消非空约束

如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：

ALTER TABLE employees MODIFY (eid NUMBER(6));

2. 唯一性约束

唯一性(Unique)约束条件用于保证字段或者字段的组合不出现重复值。当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是 NULL 值。

唯一性约束条件可以在建表同时建立，也可以在建表以后再建立。

添加唯一性约束

在建表 employees 的同时，在 eid、email 列上创建唯一约束条件，并在建表后在 name 列上建立一个名为 employees_name_uk 的唯一约束条件：

DROP TABLE employees ; --将表删掉重新创建
CREATE TABLE employees (
     eid NUMBER(6) UNIQUE,
     name VARCHAR2(30),
     email VARCHAR2(50),
     salary NUMBER(7, 2),
     hiredate DATE CONSTRAINT employees_email_uk UNIQUE(email)
     );

在建表之后增加唯一性约束条件：

ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE(name);

3. 主键约束

主键约束的意义

主键(Primary Key)约束条件从功能上看相当于非空（NOT NULL）且唯一（UNIQUE）的组合。主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。

主键可以用来在表中唯一的确定一行数据。一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。

主键选取的原则

主键应是对系统无意义的数据
永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
主键不应包含动态变化的数据，如时间戳
主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
主键尽量建立在单列上

添加主键约束

在建表时添加主键约束条件：

CREATE TABLE employees2 (
     eid NUMBER(6) UNIQUE CONSTRAINT  employees2_eid_pk PRIMARY KEY (eid)
     name VARCHAR2(30),
     email VARCHAR2(50),
     salary NUMBER(7, 2),
     hiredate DATE
 );

建表后创建主键约束条件，并自定义约束条件名称：

CREATE TABLE employees3 (
     eid NUMBER(6),
     name VARCHAR2(30),
     email VARCHAR2(50),
     salary NUMBER(7, 2),
     hiredate DATE
 );
 ALTER TABLE employees3     ADD CONSTRAINT           employees3_eid_pk PRIMARY KEY (eid);

4. 外键约束

外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。比如 emp 表的 deptno 列参照 dept 表的 deptno 列，则 dept 称作主表或父表，emp 表称作从表或子表。

添加外键约束

先建表，在建表后建立外键约束条件：

CREATE TABLE employees4 (
     eid NUMBER(6),
     name VARCHAR2(30),
     salary NUMBER(7, 2),
     deptno NUMBER(4)
 );

 ALTER TABLE employees4     ADD CONSTRAINT employees4_deptno_fk     FOREIGN KEY (deptno) REFERENCES dept(deptno);

外键约束对一致性的维护

外键约束条件包括两个方面的数据约束：

从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为 NULL；
当主表参照列的值被从表参照时，主表的该行记录不允许被删除。

--成功DML语句：
 INSERT INTO employees4(eid, name, deptno) VALUES(1234, ‘rose tyler’, 40);--成功
 INSERT INTO employees4(eid, name, deptno) VALUES(1235, ‘martha jones’, NULL); --成功

 --失败DML语句：
 INSERT INTO   employees4(eid, name, deptno) VALUES(1236, 'donna noble', 50);         --失败，不存在部门50
 DELETE FROM dept WHERE deptno  = 40;      --失败，40被参照，不允许删除

外键约束对性能的降低

如果在一个频繁 DML 操作的表上建立外键，每次 DML 操作，都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去。

另外外键确定了主从表的先后生成关系，有时会影响业务逻辑。

关联不一定需要外键约束

如果业务逻辑要求保证数据完整性，可由程序或触发器控制，不一定需要外键约束。

另外为了简化开发，维护数据时不用考虑外键约束，以及大量数据 DML 操作时不需考虑外键耗费时间。

5. 检查约束

检查(Check)约束条件用来强制在字段上的每个值都要满足 Check 中定义的条件。当定义了 Check 约束的列新增或修改数据时，数据必须符合 Check 约束中定义的条件。

添加检查约束

员工的薪水必须大于 2000 元，增加检查约束：

ALTER TABLE employees4 ADD CONSTRAINT employees4_salary_check CHECK (salary > 2000);

当插入大于 2000 的数据，操作成功：

INSERT INTO employees4(eid, name, salary, deptno)  VALUES(1236, 'donna noble', 2500, 40);

试图修改职员的薪水为 1500 元，更新失败：

UPDATE employees4 SET salary = 1500 WHERE eid = 1236;

MySQL

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

MYSQL 中，常用的几种约束：

约束类型	主键	外键	唯一	非空	自增	默认值	检查
关键字	primary key	foreign key	unique	not null	auto_increment	default	check

1. 主键约束 primary key

主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。

每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。

当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

-- 基本模式
create table temp( 
id int primary key,
name varchar(20)
 );

-- 组合模式
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);

-- 删除主键约束
alter table temp drop primary key;

-- 添加主键约束
alter table temp add primary key(id,name);

-- 修改主键约束
alter  table temp modify id int primary key；

2. 外键约束 foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

-- 基本模式
 -- 主表
 create table temp(
    id int primary key,
    name varchar(20)
 );

 -- 副表
 create table temp2(
    id int,
    name varchar(20),
    classes_id int,
    foreign key(id) references temp(id)
 );


 -- 多列外键组合，必须用表级别约束语法
 -- 主表
 create table classes(
    id int,
    name varchar(20),
    number int,
    primary key(name,number)
 );

 -- 副表
 create table student(
    id int auto_increment primary key,
    name varchar(20),
    classes_name varchar(20),
    classes_number int,
    /*表级别联合外键*/
    foreign key(classes_name, classes_number) references classes(name, number) 
 );


 -- 删除外键约束
 alter table student drop foreign key student_id;


 -- 增加外键约束
 alter table student add foreign key(classes_name, classes_number) references classes(name, number);

3. 唯一约束 unique

唯一约束是指定 table 的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个 null。
同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

-- 创建表时设置，表示用户名、密码不能重复
 create table temp(
     id int not null ,
     name varchar(20),
     password varchar(10),
     unique(name,password)
 );

 -- 添加唯一约束
 alter table temp add unique (name, password);

 -- 修改唯一约束
 alter table temp modify name varchar(25) unique;

 -- 删除约束
 alter table temp drop index name;

4. 非空约束 not null 与默认值 default

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null 类型特征：所有的类型的值都可以是 null，包括 int、float 等数据类型

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
        id int not null,
        name varchar(255) not null default  'abc',
        sex char null
)；

-- 增加非空约束
alter table temp modify sex varchar(2) not null;

-- 取消非空约束
alter table temp modify sex varchar(2) null;

-- 取消非空约束，增加默认值
alter table temp modify sex varchar(2) default 'abc' null;

5. 自增序列约束 AUTO_INCREMENT

MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用 MySQL 序列来实现。

MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。

实例 1¹
以下实例中创建了数据表 insect， insect 表中 id 无需指定值可实现自动增长。

mysql> CREATE TABLE insect
     -> (
     -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     -> PRIMARY KEY (id),
     -> name VARCHAR(30) NOT NULL, # type of insect
     -> date DATE NOT NULL, # date collected
     -> origin VARCHAR(30) NOT NULL # where collected
 );
 Query OK, 0 rows affected (0.02 sec)
 mysql> INSERT INTO insect (id,name,date,origin) VALUES
     -> (NULL,'housefly','2001-09-10','kitchen'),
     -> (NULL,'millipede','2001-09-10','driveway'),
     -> (NULL,'grasshopper','2001-09-10','front yard');
 Query OK, 3 rows affected (0.02 sec)
 Records: 3  Duplicates: 0  Warnings: 0
 mysql> SELECT * FROM insect ORDER BY id;
 +----+-------------+------------+------------+
 | id | name        | date       | origin     |
 +----+-------------+------------+------------+
 |  1 | housefly    | 2001-09-10 | kitchen    |
 |  2 | millipede   | 2001-09-10 | driveway   |
 |  3 | grasshopper | 2001-09-10 | front yard |
 +----+-------------+------------+------------+
 3 rows in set (0.00 sec)

实例 2²


mysql> create table t_test1(id int,name varchar(1000));
Query OK, 0 rows affected (0.12 sec)

# 修改id字段为主键字段，且自增

mysql> alter table t_test1 modify id int primary key auto_increment ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 插入数据，自动填充id

mysql> insert into t_test1(name) values('xiaoming');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_test1;
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
+----+----------+
1 row in set (0.00 sec)

# 修改t_test1 表的自增起始值

mysql> alter table t_test1 auto_increment = 1000;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t_test1(name) values('xiaoming');
Query OK, 1 row affected (0.01 sec)

# 自增序列从起始值开始

mysql> select * from t_test1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
| 1000 | xiaoming |
+------+----------+
2 rows in set (0.00 sec)

获取 AUTO_INCREMENT 值

在 MySQL 的客户端中你可以使用 SQL 中的 LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的 AUTO_INCREMENT 列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：


mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);

设置序列的开始值

一般情况下序列的开始值为 1，但如果你需要指定一个开始值 100，那我们可以通过以下语句来实现：


mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL,
-> date DATE NOT NULL,
-> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者你也可以在表创建成功后，通过以下语句来实现：


mysql> ALTER TABLE t AUTO_INCREMENT = 100;

6. 检查约束 check

MySQL 检查约束（CHECK）可以通过 CREATE TABLE 或 ALTER TABLE 语句实现，根据用户实际的完整性要求来定义。它可以分别对列或表实施 CHECK 约束。但是 mysql 手册里写的很清楚：“所有的存储引擎均对 CHECK 子句进行分析，但是忽略 CHECK 子句。”所以虽然可以这样写可并不会起到约束作用。

检查约束使用 CHECK 关键字，具体的语法格式如下：


CHECK <表达式>

其中：< 表达式 > 指的就是 SQL 表达式，用于指定需要检查的限定条件。
若将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为基于列的 CHECK 约束。
在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

注意：若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK 约束。该约束可以同时对表中多个列设置限定条件。

在创建表时设置检查约束

在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000，输入的 SQL 语句和运行结果如下所示。


mysql> CREATE TABLE tb_emp7
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> CHECK(salary>0 AND salary<100),
-> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
-> );
Query OK, 0 rows affected (0.37 sec)

在修改表时添加检查约束

修改表时设置检查约束的语法规则如下：


ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

修改 tb_dept 数据表，要求 id 字段值大于 0，输入的 SQL 语句和运行结果如下所示。


mysql> ALTER TABLE tb_emp7
-> ADD CONSTRAINT check_id
-> CHECK(id>0);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

解决方法：使用触发器实现 CHECK 检查约束的功能。

（1）创建 tb_student（学生信息表）。


-- 创建学生信息表
CREATE TABLE tb_student
(
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(30),
   age INT NOT NULL
);

（2）创建检查年龄（age）字段是否有效的触发器。


-- 创建触发器
CREATE TRIGGER trg_tb_student_insert_check BEFORE INSERT
ON tb_student FOR EACH ROW
BEGIN
DECLARE msg varchar(100);

IF NEW.age <= 0 OR NEW.age >= 100
THEN
SET msg = CONCAT('您输入的年龄值：',NEW.age,' 为无效的年龄，请输入0到100以内的有效数字。');
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
END IF;

END;

（3）编写测试语句。


INSERT INTO tb_student(name,age) VALUES('Kevin',120);


> [MySQL 约束类型](https://www.cnblogs.com/jennyyin/p/7895400.html)
> [mysql常见六大约束](https://blog.csdn.net/weixin_42193489/article/details/96646627)
> [mysql检查约束怎么写](https://www.php.cn/mysql-tutorials-418571.html)