SQL 检查约束

• 检查约束是应用于表的列的验证或规则。
• 当我们对任何列应用检查约束时，它会在插入记录时检查特定的值。
• 检查约束可以在表级别和列级别上创建。
• 检查约束只能应用于单个列，但在单个表中可以有多个检查约束。

让我们看一些实际例子更加清楚地理解这个概念。我们将使用MariaDB数据库来编写所有的查询。

1.列级别的检查约束

要在列级别应用检查约束，我们必须在列名后面指定检查约束。

语法：

CREATE TABLE TableName(ColumnName1 Datatype(size), ColumnName2 Datatype(size), ColumnName3 Datatype(size) Constraint ConstraintName CHECK(ColumnName CONDITION Value),…, ColumnNameN Datatype(size));

我们将编写一个查询来创建一个表，并在其中的一个列上指定列级检查约束：

CREATE TABLE employee(E_ID INT NOT NULL, Name VARCHAR(40), Salary INT, City VARCHAR(20), Designation VARCHAR(40), Date_of_Joining Date NOT NULL CHECK(Date_of_Joining > "2019-02-01"), Age INT, PRIMARY KEY(E_ID));

在上面的查询中，我们在Date_of_Joining列上指定了CHECK约束。根据这个约束，只有在加入日期在“2019-02-01”之后的记录才允许被插入到Date_of_Joining列中。

为了验证CHECK约束是否被创建在Date_of_Joining列上，我们将执行以下查询：

SHOW CREATE TABLE employee;

现在，我们将尝试在员工表中插入一条记录，其中员工的入职日期在“2019-02-01”之前。

INSERT INTO employees(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-01-01", 26);

约束失败错误是在我们尝试插入一个入职日期为“2019-01-01”的员工时出现的。因为我们在”Date_of_Joining”列上应用了检查约束，所以它只允许入职日期大于”2019-02-01″的员工。

示例2：

我们将编写一个查询来创建一个表，并在一个以上的列上指定列级检查约束。

查询：

CREATE TABLE students(S_ID INT NOT NULL, S_Name VARCHAR(40), Hometown VARCHAR(20), Percentage INT NOT NULL CHECK(Percentage >90), Favourite_Subject VARCHAR(20) NOT NULL CHECK( Favourite_Subject IN('Science', 'Maths', 'English')), PRIMARY KEY (S_ID));

在上面的查询中，我们在Percentage和Favourite_Subject列上指定了CHECK约束。根据该约束，只有学生所获得的百分比高于90且学生的最喜欢的科目是科学、数学或英语的记录才允许插入到Percentage列中。

为了验证已经成功创建了Percentage和Favourite_Subject列上的check约束，我们将执行以下命令：

SHOW CREATE TABLE students;

现在，我们将尝试向学生表中插入一个记录，该记录对应的学生分数低于90。

INSERT INTO students(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 89, "Science");

当我们尝试插入一个百分比为89的学生时，会发出约束失败错误。由于我们在”Percentage”列上应用了检查约束条件，它只允许大于90的百分比。

现在，我们将尝试在学生表中插入一条记录，其中学生获得的百分比高于90。

INSERT INTO students(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 92, "Science");

成功插入了一条学生分数为92的记录。由于我们在Percentage列上应用了检查约束，它只允许百分比大于90的值。

2.表级别的检查约束

在表级别上应用检查约束，我们必须在表创建结束之前指定检查约束。

语法：

CREATE TABLE TableName(ColumnName1 Datatype(size), ColumnName2 Datatype(size), ColumnName3 Datatype(size),…, ColumnNameN Datatype(size), Constraint ConstraintName CHECK(ColumnName CONDITION Value));

示例 1：

我们将编写一个查询来创建一张表，并在其中的一列上指定一个表级别的检查约束。

CREATE TABLE employ(E_ID INT NOT NULL, Name VARCHAR(40), Salary INT, City VARCHAR(20), Designation VARCHAR(40), Date_of_Joining Date NOT NULL, Age INT, PRIMARY KEY(E_ID), CONSTRAINT Constraint_DOJ CHECK(Date_of_Joining <= "2019-02-01"));

在上面的查询中，我们指定了Date_of_Joining列上的CHECK约束。根据这个约束，Date_of_Joining列将只允许插入那些加入日期小于或等于’2019-02-01’的记录。

为了验证Date_of_Joining列上的检查约束是否成功创建，我们将执行以下命令：

SHOW CREATE TABLE employ;

现在，我们将尝试向employ表中插入一条记录，其中员工的入职日期等于’2019-02-01’。

INSERT INTO employ(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-02-01", 26);

成功插入了加入日期小于或等于“29-02-01”的记录。由于我们在Date_of_Joining列上应用了检查约束条件，它只允许小于或等于“29-02-01”的日期。

示例2：

我们将编写一个查询来创建一个表，并在多个列上指定一个表级检查约束条件。

CREATE TABLE student(S_ID INT NOT NULL, S_Name VARCHAR(40), Hometown VARCHAR(20), Percentage INT NOT NULL,  Favourite_Subject VARCHAR(20) NOT NULL, CONSTRAINT Constraint_Percentage (Percentage >90), CONSTRAINT Constraint_Fav_sub CHECK( Favourite_Subject IN('Science', 'Maths', 'English')), PRIMARY KEY (S_ID));

在上面的查询中，我们在Percentage和Favourite_Subject列上指定了CHECK约束。根据这个约束，只有学生所获得的百分比超过90，并且学生的喜欢科目是科学、数学或英语的记录才能够插入到Percentage列中。

为了验证百分比和Favourite_Subject列上的检查约束是否成功创建，我们将执行以下命令：

SHOW CREATE TABLE student;

现在，我们将尝试向学生表中插入一条记录，该记录包括学生的得分率超过90。

INSERT INTO students(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 92, "Science");

成功插入一个学生得分为92%的记录。由于我们在百分比列上应用了检查约束，它只允许百分比大于90。

现在，我们将尝试插入一个学生表中学生得分低于90%的记录。

INSERT INTO student(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 89, "Science");

当我们尝试插入一个百分比为89的学生时，会出现约束失败的错误。由于我们在百分比列上应用了检查约束，它只允许大于90的百分比。

3.表创建后的检查约束

在某些情况下，我们需要在表创建后对某列应用检查约束。在这种情况下，我们必须使用ALTER命令来对已创建的表应用检查约束。

语法：

ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK(ColumnName CONDITION Value);

示例 1:

假设我们创建了一个没有任何约束的员工表，之后我们决定在其中的一列上添加一个约束。然后我们将执行以下查询:

ALTER TABLE employee ADD CONSTRAINT Constraint_Age CHECK (Age <= 21);

为了验证年龄列上的检查约束是否成功创建，我们将执行以下命令：

SHOW CREATE TABLE employee;

现在，我们将尝试在员工表中插入一条记录，其中员工的年龄超过21岁。

INSERT INTO employees(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-02-02", 22);

“约束失败”错误是在我们尝试插入年龄超过21岁的员工时发出的。由于我们在”年龄”列上应用了检查约束条件，因此它只允许年龄值小于或等于21。

示例2：

假设我们创建了一个没有任何约束条件的员工表，后来又决定在其中一列上添加约束条件。然后我们将执行以下查询：

ALTER TABLE employ ADD CONSTRAINT Constraint_Age CHECK (Date_of_Joining <= "2019-01-01");

为了验证日期加入列上的检查约束是否成功创建，我们将执行以下命令：

SHOW CREATE TABLE employ;

现在，我们将尝试在employ表中插入一条记录，其中员工的入职日期在’2019-01-01’之后。

INSERT INTO employ(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-02-02", 26);

当我们尝试插入加入日期为’2019-02-02’的员工时，会发出约束失败的错误。由于我们在Date_of_Joining列上应用了检查约束，它只允许加入日期在’2019-01-01’之下或等于’2019-01-01’的数据。

4.移除检查约束

假设我们在表的某个列上创建了一个检查约束。后来，我们决定将该约束从列中移除。那么，在这种情况下，我们将使用ALTER命令来移除检查约束。

语法：

ALTER TABLE TableName DROP CHECK ConstraintName;

示例 1:

假设我们在学生表的某个列上设置了一个检查约束。然后，我们决定移除该约束。

为了验证所创建的约束，我们将执行以下查询:

SHOW CREATE TABLE students;

将来执行以下命令来移除名为Constraint_Fav_sub的约束：

ALTER TABLE students DROP CHECK Constraint_Fav_sub;

我们将再次执行SHOW CREATE TABLE命令来验证检查约束是否成功移除。

SHOW CREATE TABLE students;

示例2：

假设我们在员工表的一列上分配了一个检查约束条件。后来，我们决定删除该约束条件。

为了验证创建的约束条件，我们将执行以下查询：

SHOW CREATE TABLE employee;

我们将执行以下命令来移除名为 Constraint_DOJ 的约束：

ALTER TABLE employee DROP CHECK Constraint_DOJ;

我们将再次执行SHOW CREATE TABLE命令，以验证检查约束已成功移除。

SHOW CREATE TABLE employees;