How to write database check constraints?

In a database, you can restrict the values in a column using a CHECK constraint. This constraint sets certain conditions that must be met when inserting or updating rows.

The syntax for a CHECK constraint is as follows:

CREATE TABLE table_name (
    column_name data_type CONSTRAINT constraint_name CHECK (condition)
);

table_name represents the name of the table, column_name represents the name of the column, data_type represents the data type of the column, constraint_name represents the name of the constraint, and condition represents the condition that satisfies the constraint.

Here are some examples:

1. Values in the column must be greater than 0.

CREATE TABLE employees (
    id INT,
    salary DECIMAL CONSTRAINT check_salary CHECK (salary > 0)
);

1. Restrict values in the column to be within a specified range.

CREATE TABLE students (
    id INT,
    age INT CONSTRAINT check_age CHECK (age >= 18 AND age <= 25)
);

1. Restricting values in a column to match certain regular expressions:

CREATE TABLE emails (
    id INT,
    email VARCHAR(255) CONSTRAINT check_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

1. Values in the column must adhere to the relationships between other columns.

CREATE TABLE orders (
    id INT,
    total_amount DECIMAL,
    discount DECIMAL CONSTRAINT check_discount CHECK (discount >= 0 AND discount <= total_amount)
);

The above are some common examples of CHECK constraints, you can customize more constraint conditions based on specific needs.