MySQL - 检查约束

• MySQL 检查约束
• 带触发器的检查约束
• 在单列上添加检查约束
• 在多列上添加检查约束
• 在现有表上添加检查约束
• 删除检查约束
• 使用客户端程序的检查约束

MySQL 检查约束

MySQL 检查约束是一种可应用于列的条件，以确保该列中插入或更新的数据满足指定条件。如果条件不满足，数据库将拒绝该操作以维护数据完整性。

带触发器的检查约束

MySQL 中的触发器用于自动执行一组 SQL 语句，以响应数据库中的特定事件，例如 INSERT、UPDATE 或 DELETE 操作。

带触发器的检查约束使我们能够根据数据更改自动执行操作。

示例

假设我们已经使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表，如下所示 -

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下查询使用 INSERT 语句将值插入 CUSTOMERS 表 -

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedab​​ad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

得到的表如下 −

现在，我们将添加一个检查约束，以确保 CUSTOMERS 表中客户的年龄大于或等于 18 岁。此外，我们将创建一个触发器，当尝试插入年龄小于 18 岁的记录时，它将引发错误并阻止插入 -

-- Creating a Trigger
DELIMITER //
CREATE TRIGGER check_age_trigger
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
   IF NEW.AGE < 18 THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Age must be 18 or older';
   END IF;
END;
//
DELIMITER ;

-- 添加检查约束
ALTER TABLE CUSTOMERS
ADD CONSTRAINT check_age_constraint CHECK (AGE >= 18);

输出

我们得到如下所示的输出 -

Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

在单列上添加检查约束

我们可以在创建表时，通过在列名后指定检查约束来对列应用检查约束。

语法

以下是在列上指定检查约束的语法 -

CREATE TABLE table_name (
   column1 datatype(size),
   column datatype(size) constraint constraintName
   CHECK Check(columnName condition value),..., column datatype (size)
);

示例

在此示例中，我们创建一个名为 EMPLOYEES 的表，并在其中一列上指定列级检查约束 -

CREATE TABLE EMPLOYEES(
   EID INT NOT NULL,
   NAME VARCHAR(40),
   AGE INT NOT NULL CHECK(AGE>=20),
   CITY VARCHAR(30),
   C_Phone VARCHAR(12) NOT NULL UNIQUE
);

我们可以通过在 EMPLOYEES 表中插入一个不满足条件的值来验证检查约束是否正常工作 -

INSERT INTO EMPLOYEES
VALUES (1, 'John', 19, 'New York', '09182829109');

输出

获得如下所示的输出 -

ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

在多个列上添加检查约束

我们可以通过在列名后指定每个列的约束来在表的多个列上添加检查约束。

示例

在下面的示例中，我们创建一个名为 STUDENTS 的表，并在多个列(AGE 和 FEE)上指定列级检查约束 -

CREATE TABLE STUDENTS(
   SID INT NOT NULL,
   NAME VARCHAR(20),
   AGE INT NOT NULL CHECK(AGE<=24),
   CITY VARCHAR(30),
   FEE NUMERIC NOT NULL CHECK(FEE>=15000)
);

现在，我们可以插入记录了，但如果我们尝试插入违反这些约束的记录，数据库将会拒绝。

这里，我们插入的是一条有效记录 -

INSERT INTO STUDENTS
VALUES (001, 'Robert', 21, 'LA', 17000);

我们可以在下面的输出中看到插入成功，因为年龄在允许范围内，并且费用符合指定条件 -

Query OK, 1 row affected (0.01 sec)

在这里，我们尝试插入一条违反约束的记录 -

INSERT INTO STUDENTS
VALUES (002, 'James', 25, 'Barcelona', 10000);

我们可以看到插入操作失败，因为年龄超过了 24 岁，违反了约束。

ERROR 3819 (HY000): Check constraint 'students_chk_1' is violated.

在现有表上添加检查约束

我们还可以使用 ALTER 语句在 MySQL 中为现有表添加检查约束。我们必须确保该约束满足表中现有记录的条件。

语法

ALTER TABLE table_name 
ADD CONSTRAINT ConstraintName 
CHECK(ColumnName condition Value);

示例

在下面的示例中，我们将向上面创建的 CUSTOMERS 表的 AGE 列添加检查约束 -

ALTER TABLE CUSTOMERS 
ADD CONSTRAINT Constraint_Age 
CHECK (AGE >= 21);

输出

以下是上述代码的输出 -

Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0

删除检查约束

我们可以使用 ALTER 语句和 DROP 语句来删除现有约束。

语法

以下是从表中删除约束的语法 -

ALTER TABLE table_name
DROP CONSTRAINT constraint_set;

示例

以下示例从上面创建的 CUSTOMERS 表中的 AGE 列中删除现有约束 -

ALTER TABLE CUSTOMERS 
DROP CONSTRAINT Constraint_Age;

输出

执行上述代码后，我们得到以下输出 -

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用客户端程序执行检查约束

我们也可以使用客户端程序执行检查约束。

语法

$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)"
$mysqli->query($sql);

sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
con.query(sql);

String sql = "CREATE TABLE EMPLOYEES( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE)";
statement.execute(sql);

create_table_query = 'CREATE TABLE EMPLOYEES(EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE);
cursorObj.execute(create_table_query)

示例

以下是程序 -

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
if($mysqli->query($sql)){
    printf("Table created successfully...!
");
}
//让我们插入一些年龄大于 20 的记录
$sql = "INSERT INTO EMPLOYEES VALUES(1, 'Jay', 30, 'Hyderabad', '223233')";
if($mysqli->query($sql)){
    printf("First record(age>20) inserted successfully...!
");
}
$sql = "INSERT INTO EMPLOYEES VALUES(2, 'John', 35, 'Lucknow', '213032')";
if($mysqli->query($sql)){
    printf("Second record(age>20) inserted successfully...!
");
}
//插入年龄小于20的员工记录之前的表记录;
$sql = "SELECT * FROM EMPLOYEES";
printf("Table records(before inserting emp record agequery($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row['EID'],
        $row['NAME'],
        $row['AGE'],
        $row['CITY'],
        $row['C_Phone']);
        printf("
");
    }
}
//让我们插入一些年龄小于 20 的记录
$sql = "INSERT INTO EMPLOYEES VALUES(3, 'Vinnet', 18, 'Hyderabad', '228151')";
if($mysqli->query($sql)){
    printf("Third record(agequery($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("EId: %d, NAME: %s, AGE: %d, CITY %s, C_Phone %d",
        $row['EID'],
        $row['NAME'],
        $row['AGE'],
        $row['CITY'],
        $row['C_Phone']);
        printf("
");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully...!
First record(age>20) inserted successfully...!
Second record(age>20) inserted successfully...!
Table records(before inserting emp record age

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
//连接到 MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table with check constraints
 sql = "CREATE TABLE EMPLOYEES(EID INT NOT NULL,NAME VARCHAR(40),AGE INT NOT NULL CHECK(AGE>=20),CITY VARCHAR(30),C_Phone VARCHAR(12) NOT NULL UNIQUE)";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//现在让我们插入一些记录(年龄大于 20)
sql = "INSERT INTO EMPLOYEES VALUES(1, 'Jay', 30, 'Hyderabad', '223233')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO EMPLOYEES VALUES(2, 'John', 35, 'Lucknow', '213032')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
sql = "INSERT INTO EMPLOYEES VALUES(3, 'Vinnet', 18, 'Hyderabad', '228151')";
    con.query(sql, function(err, result){
        if (err) throw err;
        console.log(result);
        });
sql = "SELECT * FROM EMPLOYEES";
con.query(sql, function(err, result){
    console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});    

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
D:	est1\checkcons.js:34
        if (err) throw err;                 ^
Error: Check constraint 'employees_chk_1' is violated.    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CheckConstraints {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //创建表
            String sql = "CREATE TABLE EMPLOYEES( EID INT NOT NULL, NAME VARCHAR(40), AGE INT NOT NULL CHECK(AGE>=20), CITY VARCHAR(30), C_Phone VARCHAR(12) NOT NULL UNIQUE)";
            st.execute(sql);
            System.out.println("Employees table created successfully...!");
            //让我们插入一些记录
            String sql1 = "INSERT INTO EMPLOYEES VALUES (1, 'John', 19, 'New York', '09182829109')";
            st.execute(sql1);
            System.out.println("Record inserted successfully....!");
            //打印表记录
            String sql2 = "SELECT * FROM EMPLOYEES";
            rs = st.executeQuery(sql2);
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String city = rs.getString("city");
                String c_phone = rs.getString("C_Phone");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", City: " + city + ", C_phone: " + c_phone);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Employees table created successfully...!
java.sql.SQLException: Check constraint 'employees_chk_1' is violated.    

import mysql.connector
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# 创建表
create_table_query = '''
CREATE TABLE EMPLOYEES(
    EID INT NOT NULL,
    NAME VARCHAR(40),
    AGE INT NOT NULL CHECK(AGE>=20),
    CITY VARCHAR(30),
    C_Phone VARCHAR(12) NOT NULL UNIQUE
)
'''
cursorObj.execute(create_table_query)
print("Table 'EMPLOYEES' is created successfully!")
# 描述 EMPLOYEES 表
describe = "DESC EMPLOYEES"
cursorObj.execute(describe)
print("Table Description:")
for column_info in cursorObj.fetchall():
    print(column_info)
# 插入第一条记录
try:
    sql = "INSERT INTO EMPLOYEES VALUES(1, 'Jay', 30, 'Hyderabad', '223233')"
    cursorObj.execute(sql)
    connection.commit()
    print("First record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")

# 插入第二条记录
try:
    sql = "INSERT INTO EMPLOYEES VALUES(2, 'John', 35, 'Lucknow', '213032')"
    cursorObj.execute(sql)
    connection.commit()
    print("Second record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")

# 插入年龄小于 20 的第三条记录(这将引发错误)
try:
    sql = "INSERT INTO EMPLOYEES VALUES(3, 'Vinnet', 18, 'Hyderabad', '228151')"
    cursorObj.execute(sql)
    connection.commit()
    print("Third record inserted successfully!")
except mysql.connector.Error as err:
    connection.rollback()
    print(f"Error: {err}")
# 检索记录
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# 打印记录
print("Table records.")
for record in records:
    print(record)
# 关闭游标和连接
cursorObj.close()
connection.close()

Table 'EMPLOYEES' is created successfully!
Table Description:
('EID', b'int', 'NO', '', None, '')
('NAME', b'varchar(40)', 'YES', '', None, '')
('AGE', b'int', 'NO', '', None, '')
('CITY', b'varchar(30)', 'YES', '', None, '')
('C_Phone', b'varchar(12)', 'NO', 'PRI', None, '')
First record inserted successfully!
Second record inserted successfully!
Error: 3819 (HY000): Check constraint 'employees_chk_1' is violated.
Table records.
('Thomas', 'Jay', None)
('Smith', 'John', None)