如何使用檢查約束來在MySQL中執行數據規則？

MySQL支持CHECK約束以強制域完整性，自8.0.16版本起生效；1. 創建表時添加約束：使用CREATE TABLE定義CHECK條件，如年齡≥18、薪資＞0、部門限定值；2. 修改表添加約束：用ALTER TABLE ADD CONSTRAINT限製字段值，如姓名非空；3. 使用複雜條件：支持多列邏輯和表達式，如結束日期≥開始日期且完成狀態需有結束日期；4. 刪除約束：通過ALTER TABLE DROP CONSTRAINT指定名稱刪除；5. 注意事項：需MySQL 8.0.16 、InnoDB或MyISAM引擎、顯式命名約束、布爾表達式求值及NULL處理；6. 常見用例：防止負值、驗證範圍、條件邏輯、禁止空字符串；結合NOT NULL等約束可提升數據完整性，作為應用驗證的最終保障。

Check constraints in MySQL are used to enforce domain integrity by limiting the values that can be inserted into a column. They ensure that data meets specific conditions before being written to the table. MySQL started supporting CHECK constraints with version 8.0.16, so earlier versions will parse but ignore them.

Here's how to use check constraints effectively in MySQL:

1. Adding a Check Constraint During Table Creation

You can define a CHECK constraint when creating a table using the CREATE TABLE statement.

 CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    salary DECIMAL(10,2),
    department VARCHAR(50),

    -- Enforce age to be at least 18
    CONSTRAINT chk_age CHECK (age >= 18),

    -- Ensure salary is positive
    CONSTRAINT chk_salary CHECK (salary > 0),

    -- Restrict department to specific values
    CONSTRAINT chk_department CHECK (department IN ('HR', 'Engineering', 'Sales', 'Marketing'))
);

In this example:

• chk_age ensures no employee under 18 is added.
• chk_salary prevents zero or negative salaries.
• chk_department limits entries to predefined departments.

2. Adding a Check Constraint to an Existing Table

Use ALTER TABLE ... ADD CONSTRAINT to add a check constraint after table creation.

 ALTER TABLE employees
ADD CONSTRAINT chk_name_not_empty
CHECK (name != '');

This prevents empty strings from being inserted into the name field.

⚠️ Note: The constraint is evaluated for every INSERT or UPDATE . If existing data violates the condition, the ALTER TABLE statement will fail.

3. Using Complex Conditions in Check Constraints

You can use expressions and multiple columns in a check constraint.

 CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    status ENUM('planned', 'active', 'completed'),

    -- Ensure end date is not before start date
    CONSTRAINT chk_date_order CHECK (end_date >= start_date),

    -- Only allow 'completed' status if end_date is set
    CONSTRAINT chk_status_completion CHECK (
        (status != 'completed') OR (status = 'completed' AND end_date IS NOT NULL)
    )
);

This ensures logical consistency between dates and status.

4. Dropping a Check Constraint

To remove a check constraint, use:

 ALTER TABLE employees
DROP CONSTRAINT chk_name_not_empty;

Make sure to know the exact constraint name. You can view constraints using:

 SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'employees' AND CONSTRAINT_TYPE = 'CHECK';

5. Important Notes and Limitations

• MySQL versions before 8.0.16 : CHECK syntax is parsed but ignored. Always verify your MySQL version.
• Storage engines : Check constraints are supported in InnoDB and MyISAM.
• Constraint naming : It's good practice to name constraints explicitly for easier management.
• Boolean expressions : The condition must evaluate to a boolean (true/false). NULL is treated as unknown and may allow insertion unless explicitly handled.
• Performance : Check constraints have minimal overhead but are evaluated on every write.

6. Common Use Cases

• Prevent negative values: CHECK (price > 0)
• Validate ranges: CHECK (rating BETWEEN 1 AND 5)
• Enforce conditional logic: CHECK (status != 'archived' OR archive_date IS NOT NULL)
• Disallow blank strings: CHECK (LENGTH(trim(name)) > 0)

? Tip: Combine CHECK with NOT NULL and other constraints for robust data integrity.

Using check constraints helps maintain clean, reliable data directly at the database level, reducing the burden on application logic. While not a replacement for application validation, they serve as a final safeguard against invalid data.

Basically, if your MySQL version supports it (8.0.16 ), use CHECK constraints to enforce business rules where they belong — in the database.

以上是如何使用檢查約束來在MySQL中執行數據規則？的詳細內容。更多資訊請關注PHP中文網其他相關文章！