SQL约束

博客 动态
0 142
羽尘
羽尘 2023-03-11 16:26:29
悬赏：0 积分 收藏

SQL约束

1、约束

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

1.1、非空约束NOT NULL

NOT NULL 约束强制列不接受 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 而不是空串指定。

1.2唯一约束UNIQUE

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

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

1.2.1CREATE TABLE 时的 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`)
)

1.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`)

1.2.3撤销UNIQUE约束

如需撤销 UNIQUE 约束 :

MySQL

ALTER TABLE `Persons`
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access

ALTER TABLE `Persons`
DROP CONSTRAINT uc_PersonID

1.3主键约束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 只是为了保证域/域组的唯一性。

1.3.1CREATE 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)

1.3.2ALTER 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 值（在表首次创建时）。

删除某表的主键约束：

ALTER TABLE `table_name` DROP PRIMARY KEY;

1.4外键约束

1.4.1什么是外键

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

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

1.4.2外键的作用

外键最根本的作用：保证数据的完整性和一致性。

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

1.4.3外键约束

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

1.4.3.1CREATE 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 表示约束，后面接约束名称，常用于创建约束和删除约束；

1.4.3.2ALTER 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)

1.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

1.5检查CHECK

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

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

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

定义 CHECK 约束条件在某种程度上类似于编写查询的 WHERE 子句，使用不同的比较运算符（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）编写其布尔表达式，该布尔表达式将返回 TRUE、FALSE 或 UNKNOWN 。

当条件中存在 NULL 值时，CHECK约束将返回 UNKNOWN 值。

CHECK 约束主要用于通过将插入的值限制为遵循定义的值、范围或格式规则的值来强制域完整性。

CREATE DATABASE IF NOT EXISTS hardy_db 
	default character set utf8mb4 collate utf8mb4_0900_ai_ci;

USE hardy_db;

DROP TABLE IF EXISTS lesson;

创建表结构时可以使用 CHECK 约束，也可以给已创建的表增加 CHECK 约束。

??我们举一个例子感受一下它的用法：

假如我们想创建一个简单的课程表 courses ，表中每一条数据记录着课程编号 id、课程名称 name 、学生总数 student_count 、创建课程时间 created_at 以及授课教师编号 teacher_id。其中课程编号 id 为主键。

根据基本常识，学生总数 student_count 一定是非负值，在这里我们设置它必须为正整数，可以使用 CHECK 约束。

在不同的 SQL 软件中，语法会有些不同，在本文中会介绍 CHECK 约束在各个 SQL 软件中的使用。

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

在创建课程表 courses 时，给学生总数 student_count 字段加上一个大于 0 的约束。
[CHECK (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 约束

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：被约束内容

1.5.2表已存在时添加CHECK约束

课程表 courses 已存在的情况下为学生总数 student_count 字段添加一个大于 0 的 CHECK 约束：
MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE `courses`
	ADD CHECL (`student_count` > 0)

或

ALTER TABLE `courses`
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);

1.5.3撤销CHECK约束

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

ALTER TABLE `courses` 
DROP CHECK chk_courses

SQL Server / Oracle / MS Access：

ALTER TABLE `courses` 
DROP CONSTRAINT chk_courses

1.6默认约束 DEFAULT

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

1.6.1 DEFAULT 约束用法

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

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

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

1.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()
)

1.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`

1.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 @ 2023-03-11 16:20  JavaCoderPan  阅读(0)  评论(0)  编辑  收藏  举报
上一篇：三天吃透MySQL面试八股文
下一篇：使用shell方式对vcenter进行补丁升级
回帖
    羽尘

    羽尘 （王者 段位）

    2335 积分 (2)粉丝 (11)源码

     

    温馨提示

    亦奇源码

    最新会员