SQL约束

1. 非空约束 NOT NULL

1.1 约束

在 SQL 中，约束是规定表中的数据规则。若存在违反约束的行为，行为就会被阻止。它能帮助管理员更好地管理数据库，并且确保数据库中数据的正确性和有效性。例如在后台的数据库设计中对要输入的数据进行核查或判断，再决定是否写入数据库，这都是约束的应用。

1.2 NOT NULL

NOT NULL 约束强制列不接受 NULL 值，强制字段始终包含值，这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。

先通过一个例子感受一下 NOT NULL 的作用

下面的 SQL 强制 ID 列、 LastName 列以及 FirstName 列不接受 NULL 值：

CREATE TABLE `Persons` (
    `ID` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255) NOT NULL,
    `Age` int
);

示例代码

在一个已创建的表的 Age 字段中添加 NOT NULL 约束如下所示：

ALTER TABLE `Persons`
MODIFY `Age` int NOT NULL;

在一个已创建的表的 Age 字段中删除 NOT NULL 约束如下所示：

ALTER TABLE `Persons`
MODIFY `Age` int NULL;

注意

不要把 NULL 值与空串相混淆。NULL 值是没有值，

它不是空串。如果指定' '（两个单引号，其间没有字符），这
在 NOT NULL 列中是允许的。空串是一个有效的值，它不是无
值。NULL 值用关键字 NULL 而不是空串指定。

2.唯一约束 UNIQUE

在前面的学习中我们知道了 NOT NULL 约束是强制列不接受 NULL 值，在有些情况下，我们不希望一个表中出现重复的记录，这时候我们需要用到 UNIQUE 约束来解决这些问题。下面我们来介绍一下。

  • UNIQUE 约束唯一标识数据库表中的每条记录
  • UNIQUE 和 主键约束均为列或列集合提供了唯一性的保证
  • 主键约束会自动定义一个 UNIQUE 约束，或者说主键约束是一种特殊的 UNIQUE 约束。但是二者有明显的区别：每个表可以有多个 UNIQUE 约束，但只能有一个主键约束。

2.1 CREATE TABLE 时的 UNIQUE 约束

下面的 SQL 在 Persons 表创建时在 P_Id 列上创建 UNIQUE 约束：

MySQL

CREATE TABLE `Persons`
(
`P_Id` int NOT NULL,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255),
UNIQUE (`P_Id`)
)

SQL Server / Oracle / MS Access

CREATE TABLE `Persons`
(
`P_Id` int NOT NULL UNIQUE,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255)
)

命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE `Persons`
(
`P_Id` int NOT NULL,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255),
CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)
)

2.2 ALTER TABLE 时的 UNIQUE 约束

当表已被创建时，在 P_Id 列创建 UNIQUE 约束:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
ADD UNIQUE（`P_Id`）

当表已被创建时，需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
ADD CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)

2.3 撤销 UNIQUE 约束

如需撤销 UNIQUE 约束 :

MySQL

ALTER TABLE `Persons`
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
DROP CONSTRAINT uc_PersonID


3. 主键约束 PRIMARY KEY

3.1 初识主键约束

在前面的学习中我们学会了 NOT NULL 约束和
UNIQUE 约束的使用，今天带大家来认识主键约束，也叫 PRIMARY KEY 约束。

PRIMARY KEY 约束唯一标识数据库表中的每条记录 ，简单的说，PRIMARY KEY = UNIQUE + NOT NULL ,从技术的角度来看，PRIMARY KEY 和 UNIQUE 有很多相似之处。但还是有以下区别：

  • NOT NULL UNIQUE 可以将表的一列或多列定义为唯一性属性，而 PRIMARY KEY 设为多列时，仅能保证多列之和是唯一的，具体到某一列可能会重复。
  • PRIMARY KEY 可以与外键配合，从而形成主从表的关系，而 NOT NULL UNIQUE 则做不到这一点

如：

表一：用户 id (主键)，用户名

表二: 银行卡号 id (主键)，用户 id (外键)

则表一为主表，表二为从表。

  • 更大的区别在逻辑设计上。 PRIMARY KEY 一般在逻辑设计中用作记录标识，这也是设置 PRIMARY KEY 的本来用意，而 UNIQUE 只是为了保证域/域组的唯一性。

3.2 CREATE TABLE 时 添加 PRIMARY KEY 约束

👇我们可以通过下面的实例来感受一下如何添加 PRIMARY KEY 约束。

前面我们创建了个人信息数据表 Persons，我们希望每个人的标识符 P_Id 都是唯一的。
下面的 SQL 在 个人信息表 Persons 创建时，在 P_Id 列上添加 PRIMARY KEY 约束：

MYSQL

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255),
    PRIMARY KEY (`P_Id`)
);

执行输出结果

mysql> CREATE TABLE `Persons`
    -> (
    ->     `P_Id` int NOT NULL,
    ->     `LastName` varchar(255) NOT NULL,
    ->     `FirstName` varchar(255),
    ->     `Address` varchar(255),
    ->     `City` varchar(255),
    ->     PRIMARY KEY (`P_Id`)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| P_Id      | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

我们通过 desc table_name 查看建立的数据表 Persons 可以发现，P_Id 的 Key 属性为 PRI ，即设定为 PRIMARY KEY 。

SQL Server / Oracle / MS Access

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL PRIMARY KEY,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255)
)

上述语句中 NOT NULL 为非空约束，即输入值必须不为空否则会报错。

如需命名并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)
)

注释：
在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的。

3.3 ALTER TABLE 时添加主键约束

如果我们在创建 Persons 时没有指定创建主键约束，此时是否需要删除表再重新写带有主键约束的建表语句呢？这里我们可以使用 ALTER TABLE 语句在创建表后添加主键约束。

当表已被创建时，如需在 P_Id 列创建 PRIMARY KEY 约束：

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
ADD PRIMARY KEY (`P_Id`)

执行输出结果

mysql> CREATE TABLE Persons
    -> (
    ->     `P_Id` int NOT NULL,
    ->     `LastName` varchar(255) NOT NULL,
    ->     `FirstName` varchar(255),
    ->     `Address` varchar(255),
    ->     `City` varchar(255)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE Persons
    -> ADD PRIMARY KEY (P_Id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| P_Id      | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

如需命名并定义多个列的 PRIMARY KEY 约束，可以使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
ADD CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)

注释：

如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

执行输出结果

mysql> CREATE TABLE `Persons`
    -> (
    ->     `P_Id` int NOT NULL,
    ->     `LastName` varchar(255) NOT NULL,
    ->     `FirstName` varchar(255),
    ->     `Address` varchar(255),
    ->     `City` varchar(255)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE `Persons`
    -> ADD CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| P_Id      | int(11)      | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   | PRI | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

3.4 撤销 PRIMARY KEY

上面学习了如何添加主键约束，那么删除主键约束呢？

👇我们可以通过下面的实例来感受一下如何删除 PRIMARY KEY 约束。

如需撤销 PRIMARY KEY 约束，我们可以通过将上述 ALTER TABLE 和 DROP 实现：

MYSQL

ALTER TABLE `Persons`
DROP PRIMARY KEY

执行输出结果

mysql> ALTER TABLE `Persons`
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| P_Id      | int(11)      | NO   |     | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

通过DESC table_name我们可以发现，Key 列上的约束已经为空了。

SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
DROP CONSTRAINT pk_PersonID

4. 外键约束 FOREIGN KEY

在上一节的学习中我们知道了主键约束及它的应用，那么这一节内容我们来了解一下外键约束的用法和应用。

4.1 什么是外键

首先在学习外键约束之前我们先来认识一下什么是外键。一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY 。

让我们看了例子，如果一个字段 X 在一张表（表 1 ）中是关键字，而在另一张表（表 2 ）中不是关键字，则称字段 X 为表 2 的外键。

4.2 外键的作用

外键最根本的作用：保证数据的完整性和一致性。这么说可能有些同学无法理解，接下来通过一个例子来深入理解一下。

现在有两张表——学生表和院系表，这里的院系就是学生表的外键，外键表是学生表，主键表是院系表。假如院系表中的某个院系被删除了，那么在学生表中要想查询这个被删除的院系号所对应的院信息就会报错，因为已经不存在这个系了，所以，删除院系表（主键表）时必须删除其他与之关联的表，这里就说明了外键的作用，保持数据的一致性、完整性。当然反过来讲，你删除学生表中的记录，并不影响院系表中的数据，你查询院系号也能正确查询。所以删除外键表中的数据并不影响主键表。

4.3 外键约束

在了解了什么是外键之后，我们来学习一下外键约束。外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。接下来在实际操做中让我们感受一下外键约束的用法。

4.3.1 CREATE TABLE 时的 SQL FOREIGN KEY 约束

👇我们可以通过下面的实例来感受一下如何添加 FOREIGN KEY 约束。

在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束：

MySQL

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL PRIMARY KEY,
`OrderNo` int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

其中

NOT NULL 表示该字段不为空
REFERENCES 表示 引用一个表

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

其中

CONSTRAINT 表示约束，后面接约束名称，常用于创建约束和删除约束；

4.3.2 ALTER TABLE 时的 SQL FOREIGN KEY 约束

当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE `Orders`
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE `Orders`
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

4.3.3 撤销 FOREIGN KEY 约束

👇我们可以通过下面的实例来感受一下如何删除 FOREIGN KEY 约束。

如需撤销 FOREIGN KEY 约束:

MySQL

ALTER TABLE `Orders`
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access

ALTER TABLE `Orders`
DROP CONSTRAINT fk_PerOrders
 

5. 检查约束 CHECK

CHECK 约束用于限制列中的值的范围，评估插入或修改后的值。满足条件的值将会插入表中，否则将放弃插入操作。 可以为同一列指定多个 CHECK 约束。

CHECK 约束既可以用于某一列也可以用于某张表：

  • 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

  • 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

定义 CHECK 约束条件在某种程度上类似于编写查询的 WHERE 子句，使用不同的比较运算符（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）编写其布尔表达式，该布尔表达式将返回 TRUE、FALSE 或 UNKNOWN 。 当条件中存在 NULL 值时，CHECK约束将返回 UNKNOWN 值。
CHECK 约束主要用于通过将插入的值限制为遵循定义的值、范围或格式规则的值来强制域完整性。

2.1 创建表（CREATE TABLE）时添加 CHECK约束

  • 在创建课程表 courses 时，给学生总数 student_count 字段加上一个大于 0 的约束。

MYSQL：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)

SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int
CHECK (`student_count` > 0),
`name` varchar(255),`student_count` int,
`created_at` date,
`teacher_id` int
)
  • 为多个列添加 CHECK 约束

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

如果想为一个表中多个字段添加约束，直接在 CHECK 关键字后的括号内添加，两个约束间使用 AND 关键字连接。

  • 为 CHECK 约束命名

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CONSTRAINT chk_courses CHECK (`student_count` > 0) ;

核心部分的基本语法为：

[CONSTRAINT <constraint name>] CHECK (<condition>)

其中：

  • 约束关键字 CONSTRAINT：表示其后面接的内容为约束
  • constraint name：为约束名称
  • 关键字 CHECK：表示检查约束
  • condition：被约束内容

2.3 表已存在时添加 CHECK 约束

  • 课程表 courses 已存在的情况下为学生总数 student_count 字段添加一个大于 0 的 CHECK 约束。

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE `courses` 
ADD CHECK ( `student_count` > 0);

或

ALTER TABLE `courses`  
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);
  • ALTER TABLE 关键字：表示修改表的定义
  • ADD 关键字：表示增加

2.4 撤销 CHECK 约束

  • 如果想要撤销 CHECK 约束，可以使用 DROP 关键字。

MYSQL：

ALTER TABLE `courses` 
DROP CHECK chk_courses

SQL Server / Oracle / MS Access：

ALTER TABLE `courses` 
DROP CONSTRAINT chk_courses


6. DEFAULT 约束

在前面的学习中我们知道了什么是主键约束和外键约束以及它们的应用，今天的我们来学习默认约束。

默认值（Default）”的完整称呼是“默认值约束（Default Constraint）”。MySQL 默认值约束用来指定某列的默认值。

6.1 DEFAULT 约束用法

  • DEFAULT 约束用于向列中插入默认值。

  • 如果没有规定其他的值，那么会将默认值添加到所有的新记录。

  • 例如女同学较多，性别就可以默认为“女”，如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

6.2 CREATE TABLE 时的 DEFAULT 约束

  • 使用 DEFAULT 关键字设置默认值约束，具体的语法规则如下所示：

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

👇下面的 SQL 在 Persons表创建时在 City 列上创建 DEFAULT 约束：

MYSQL / SQL Server / Oracle / MS Access


CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255) DEFAULT 'Sandnes'
)

👇通过使用类似 GETDATE() 这样的函数, DEFAULT 约束也可以用于插入系统值：

CREATE TABLE `Orders`
(
    `O_Id` int NOT NULL,
    `OrderNo` int NOT NULL,
    `P_Id` int,
    `OrderDate` date DEFAULT GETDATE()
)

6.3 ALTER TABLE 时的 DEFAULT 约束

👇如果表已被创建时，想要在 City 列创建 DEFAULT 约束，请使用下面的 SQL：

MYSQL

ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'SANDNES'

SQL Server / MS Access：

ALTER TABLE `Persons`
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for `City`

6.4 撤销 DEFAULT 约束

👇如需撤销 Persons表的 DEFAULT 约束 :

MYSQL:

ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT

SQL Server / Oracle / MS Access：

ALTER TABLE `Persons`
ALTER COLUMN `City` DROP DEFAULT
 
posted @   君逸堂  阅读(86)  评论(0)  编辑  收藏  举报
相关博文：
· SQL联结
· SQL分组查询/子查询
· SQL约束
· 【MySQL】04_约束
· MySQL中常见的约束（Constraints）
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
点击右上角即可分享
微信分享提示
AI IDE Trae