MySQL系列教程 --- 31、MySQL约束（Constraints）

MySQL 中的约束用于指定允许或限制哪些值/数据将存储在表中的规则。它们提供了一种合适的方法来确保表内数据的准确性和完整性。它还有助于限制将插入表中的数据类型。如果约束和数据操作之间发生任何中断，则该操作失败。

MySQL 约束的类型

MySQL 中的约束分为两种类型：

1. 列级约束：这些约束仅应用于限制特定列数据类型的单个列。
2. 表级约束：这些约束应用于整个表，限制了整个表的数据类型。

如何在 MySQL 中创建约束

我们可以在使用 CREATE TABLE 语句创建表期间定义约束。MySQL 还使用 ALTER TABLE 语句在现有表模式的情况下指定约束。

句法

以下是在表中创建约束的语法：

创建表 new_table_name (   
    col_name1 数据类型 约束，  
    col_name2 数据类型 约束，  
    col_name3 数据类型 约束，  
    …………  
);  

MySQL 中使用的约束

以下是 MySQL 中最常用的约束：

• 非空
• 查看
• 默认
• 首要的关键
• 自动递增
• 独特的
• 指数
• 枚举
• 外键

让我们详细讨论这些约束中的每一个。

非空约束

此约束指定该列不能有 NULL 或空值。下面的语句创建一个带有 NOT NULL 约束的表。

mysql>  CREATE TABLE  Student(Id  INTEGER , LastName TEXT  NOT NULL , FirstName TEXT  NOT NULL , City  VARCHAR (35));     

执行下面列出的查询以了解其工作原理：

mysql>  INSERT INTO  Student  VALUES (1,  'Hanks' ,  'Peter' ,  'New York' );   

mysql>  INSERT INTO  Student  VALUES (2,  NULL ,  'Amanda' ,  'Florida' );    

输出

在上图中，我们可以看到第一个 INSERT 查询正确执行，但第二个语句失败并给出错误，指出列 LastName 不能为空。

唯一约束

此约束确保插入到列中的所有值都是唯一的。这意味着列不能存储重复值。MySQL 允许我们在一个表中使用多个具有 UNIQUE 约束的列。下面的语句创建一个具有 UNIQUE 约束的表：

mysql>  CREATE TABLE  ShirtBrands(Id INTEGER ,  BrandName  VARCHAR (40)  UNIQUE ,  Size VARCHAR (30));    

执行下面列出的查询以了解其工作原理：

mysql>  INSERT INTO  ShirtBrands(Id, BrandName,  Size )  VALUES (1,  'Pantaloons' , 38), (2,  'Cantabil' , 40);   

mysql>  INSERT INTO  ShirtBrands(Id, BrandName,  Size )  VALUES (1,  'Raymond' , 38), (2,  'Cantabil' , 40);    

输出

在下面的输出中，我们可以看到第一个 INSERT 查询正确执行，但第二个语句失败并给出错误消息：Duplicate entry 'Cantabil' for key BrandName。

检查约束

它控制特定列中的值。它确保列中插入的值必须满足给定条件。换句话说，它确定与列关联的值在给定条件下是否有效。

在 8.0.16 版本之前，MySQL 使用此约束语法的受限版本，如下所示：

CHECK (expr)  

8.0.16版本之后，MySQL对所有存储引擎都使用CHECK约束，即表约束和列约束，如下所示：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]  

让我们了解 CHECK 约束在 MySQL 中是如何工作的。例如，以下语句创建一个表“Persons”，其中包含对“Age”列的 CHECK 约束。CHECK 约束确保列中插入的值必须满足给定条件意味着一个人的年龄应该大于或等于 18：

mysql> CREATE TABLE Persons (  
    ID int NOT NULL,  
    Name varchar(45) NOT NULL,  
    Age int CHECK (Age>=18)  
);  

执行列出的查询以将值插入表中：

mysql> INSERT INTO Persons(Id, Name, Age)   
VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);  

mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);  

输出

在下面的输出中，我们可以看到第一个 INSERT 查询成功执行，但第二个语句失败并给出错误消息：键 Age 违反了 CHECK 约束。

默认约束

此约束用于为未指定任何值的特定列设置默认值。这意味着该列必须包含一个值，包括 NULL。

例如，以下语句创建一个表“Persons”，其中包含对“City”列的 DEFAULT 约束。如果我们没有为 City 列指定任何值，它会插入默认值：

mysql> CREATE TABLE Persons (  
    ID int NOT NULL,  
    Name varchar(45) NOT NULL,  
    Age int,  
    City varchar(25) DEFAULT 'New York'  
);  

执行列出的查询以将值插入表中：

mysql> INSERT INTO Persons(Id, Name, Age, City)   
VALUES (1,'Robert', 15, 'Florida'),   
(2, 'Joseph', 35, 'California'),   
(3, 'Peter', 40, 'Alaska');  

mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Brayan', 15);  

输出

在下面的输出中，我们可以看到第一个包含所有字段的插入查询执行成功，而第二个插入语句不包含“城市”列但也执行成功。这是因为它有一个默认值。

现在，执行以下语句来验证第 4 列的默认值：

mysql> SELECT * FROM Persons;  

我们可以看到它完美运行。这意味着默认值“纽约”自动存储在城市列中。

主键约束

此约束用于唯一标识表中的每条记录。如果该列包含主键约束，则它不能为 null 或为空。一张表可能有重复的列，但它只能包含一个主键。它总是在列中包含唯一值。

以下语句创建了一个表“Person”并更清楚地解释了这个主键的使用：

CREATE TABLE Persons (  
    ID int NOT NULL PRIMARY KEY,   
    Name varchar(45) NOT NULL,   
    Age int,   
    City varchar(25));  

接下来，使用插入查询将数据存储到表中：

INSERT INTO Persons(Id, Name, Age, City)   
VALUES (1,'Robert', 15, 'Florida') ,   
(2, 'Joseph', 35, 'California'),   
(3, 'Peter', 40, 'Alaska');  

INSERT INTO Persons(Id, Name, Age, City)   
VALUES (1,'Stephen', 15, 'Florida');  

输出

在下面的输出中，我们可以看到第一个插入查询成功执行。而第二个插入语句失败并给出错误提示：主键列的条目重复。

AUTO_INCREMENT 约束

每当我们向表中插入新记录时，此约束都会自动生成一个唯一编号。通常，我们对表中的主键字段使用此约束。

我们可以通过以下示例来理解它，其中 id 列将在 Animal 表中自动递增：

mysql> CREATE TABLE Animals(  
id int NOT NULL AUTO_INCREMENT,   
name CHAR(30) NOT NULL,   
PRIMARY KEY (id));  

接下来，我们需要将值插入到“Animals”表中：

mysql> INSERT INTO Animals (name) VALUES   
('Tiger'),('Dog'),('Penguin'),   
('Camel'),('Cat'),('Ostrich');  

现在，执行以下语句以获取表数据：

mysql> SELECT * FROM Animals;  

输出

在输出中，我们可以看到我没有为自增列指定任何值，因此 MySQL 会自动为此字段按序列顺序生成唯一编号。

枚举约束

MySQL 中的 ENUM 数据类型是一个字符串对象。它允许我们在创建表时限制从列规范中允许的值列表中选择的值。它是枚举的缩写，这意味着每一列可能具有指定的可能值之一。它使用数字索引（1、2、3…）来表示字符串值。

下图创建了一个名为“shirts”的表，其中包含三列：id、name 和 size。列名“size”使用包含小、中、大和 x 大尺寸的 ENUM 数据类型。

mysql> CREATE TABLE Shirts (    
    id INT PRIMARY KEY AUTO_INCREMENT,     
    name VARCHAR(35),     
    size ENUM('small', 'medium', 'large', 'x-large')    
);  

接下来，我们需要使用以下语句将值插入到“衬衫”表中：

mysql> INSERT INTO Shirts(id, name, size)     
VALUES (1,'t-shirt', 'medium'),     
(2, 'casual-shirt', 'small'),     
(3, 'formal-shirt', 'large');  

现在，执行 SELECT 语句以查看插入到表中的值：

mysql> SELECT * FROM Shirts;  

输出

我们将得到以下输出：

索引约束

此约束允许我们非常快速且轻松地从表中创建和检索值。可以使用一列或多于一列创建索引。它以将它们插入到表中的方式为每一行分配一个 ROWID。

下图创建了一个名为“shirts”的表，其中包含三列：id、name 和 size。

mysql> CREATE TABLE Shirts (    
    id INT PRIMARY KEY AUTO_INCREMENT,     
    name VARCHAR(35),     
    size ENUM('small', 'medium', 'large', 'x-large')    
);  

接下来，我们需要使用以下语句将值插入到“衬衫”表中：

mysql> INSERT INTO Shirts(id, name, size)     
VALUES (1,'t-shirt', 'medium'),     
(2, 'casual-shirt', 'small'),     
(3, 'formal-shirt', 'large');  

现在，执行此语句以创建索引：

mysql> CREATE INDEX idx_name ON Shirts(name);  

我们可以使用下面的查询通过索引列检索数据：

mysql> SELECT * FROM Shirts USE INDEX(idx_name);  

输出

出现以下输出：

外键约束

此约束用于将两个表链接在一起。它也称为引用键。外键列匹配另一个表的主键字段。表示一个表中的外键字段引用另一表的主键字段。

让我们考虑一下这些表的结构：Persons 和 Orders。

表：Persons

CREATE TABLE Persons (  
    Person_ID int NOT NULL PRIMARY KEY,   
    Name varchar(45) NOT NULL,   
    Age int,   
    City varchar(25)  
);  

表：Orders

CREATE TABLE Orders (  
    Order_ID int NOT NULL PRIMARY KEY,  
    Order_Num int NOT NULL,  
    Person_ID int,  
    FOREIGN KEY (Person_ID) REFERENCES Persons(Person_ID)  
);  

在上面的表结构中，我们可以看到“Orders”表中的“Person_ID”字段指向“Persons”表中的“Person_ID”字段。“Person_ID”是“Persons”表中的PRIMARY KEY，而“Orders”表的“Person_ID”列是FOREIGN KEY。

输出

我们的表包含以下数据：