How to write check constraints in mysql

Mysql method of writing check constraints: 1. Use the ENUM data type. The ENUM data type allows us to define a fixed value list when creating a table, and then limit the column value to a value in the list. For example, you can create a column named gender and define it as ENUM('male', 'female'), so that the value of this column can only be 'male' or 'female'; 2. Use a trigger to trigger A trigger is a database object that automatically executes when a specific event occurs. Triggers can be used to implement more complex checking constraints.

MySQL is a commonly used relational database management system that provides rich functions to ensure data integrity and consistency. One of them is to limit the input and modification of data through constraints to ensure the validity of the data. This article will introduce how to write check constraints in MySQL.

In MySQL, you can use the following two methods to implement check constraints:

1. Use the ENUM data type: The ENUM data type allows us to define a fixed list of values ​​when creating a table, Then limit the column's value to one value in that list. For example, we can create a column called gender and define it as ENUM('Male', 'Female') so that the value of the column can only be 'Male' or 'Female'. The following is an example:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    gender ENUM('男', '女')
);

In the above example, the value of the gender column can only be 'male' or 'female'. If you try to insert other values, MySQL will report an error.

2. Use triggers: A trigger is a database object that executes automatically when a specific event occurs. We can use triggers to implement more complex check constraints. For example, let's say we have a table called students that contains an age column and we want to limit age to between 18 and 25. We can create a trigger called check_age to implement this constraint as follows:

CREATE TRIGGER check_age
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    IF NEW.age < 18 OR NEW.age > 25 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在18到25之间';
    END IF;
END;

In the above example, if an attempt is made to insert a value with age less than 18 or greater than 25, the trigger will throw a mistake.

It should be noted that triggers can only be executed at the table level, not at the column level. Therefore, if you want to apply a check constraint on a specific column, you need to write the corresponding logic in the trigger.

To summarize, MySQL provides a variety of methods to implement check constraints, including using the ENUM data type and triggers. By using these methods rationally, we can ensure the validity and consistency of data and improve the quality and reliability of the database.

The above is the detailed content of How to write check constraints in mysql. For more information, please follow other related articles on the PHP Chinese website!