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://: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)