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, 'Ahmedabad', 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 ;

-- Adding a Check Constraint
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

删除检查约束

我们可以使用带有 **DROP** 语句的 **ALTER** 语句删除现有约束。

语法

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

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...!\n");
}
//let's insert some records... whose age is greater than 20
$sql = "INSERT INTO EMPLOYEES VALUES(1, 'Jay', 30, 'Hyderabad', '223233')";
if($mysqli->query($sql)){
    printf("First record(age>20) inserted successfully...!\n");
}
$sql = "INSERT INTO EMPLOYEES VALUES(2, 'John', 35, 'Lucknow', '213032')";
if($mysqli->query($sql)){
    printf("Second record(age>20) inserted successfully...!\n");
}
//table record before inserting employee record whose age is less than 20;
$sql = "SELECT * FROM EMPLOYEES";
printf("Table records(before inserting emp record age<20): \n");
if($result = $mysqli->query($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("\n");
    }
}
//let's insert some records... whose age is less than 20
$sql = "INSERT INTO EMPLOYEES VALUES(3, 'Vinnet', 18, 'Hyderabad', '228151')";
if($mysqli->query($sql)){
    printf("Third record(age<20) inserted successfully...!\n");
}
$sql = "SELECT * FROM EMPLOYEES";
printf("Table records: \n");
if($result = $mysqli->query($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("\n");
    }
}
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<20):
EId: 2, NAME: John, AGE: 35, CITY Lucknow, C_Phone 213032
EId: 1, NAME: Jay, AGE: 30, CITY Hyderabad, C_Phone 223233
PHP Fatal error:  Uncaught mysqli_sql_exception: Check constraint 'employees_chk_1' is violated. in D:\test\checkconstraints.php:46    

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to 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....!");
    });
//now let's insert some records(age greater than 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:\test1\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://127.0.0.1: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...!");
            //create table
            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...!");
            //let's insert some records
            String sql1 = "INSERT INTO EMPLOYEES VALUES (1, 'John', 19, 'New York', '09182829109')";
            st.execute(sql1);
            System.out.println("Record inserted successfully....!");
            //lets print table records
            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
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table 
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!")
# Describing the EMPLOYEES table
describe = "DESC EMPLOYEES"
cursorObj.execute(describe)
print("Table Description:")
for column_info in cursorObj.fetchall():
    print(column_info)
# Inserting the first record
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}")

# Inserting the second record
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}")

# Inserting the third record with age less than 20 (this will raise an error)
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}")
# Retrieving records
cursorObj.execute("SELECT * FROM person_tbl")
records = cursorObj.fetchall()
# Printing the records
print("Table records.")
for record in records:
    print(record)
# Closing the cursor and connection
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)