Detailed explanation of constraints in MySQL

Detailed explanation of constraints in MySQL

In the MySQL database, constraints (constraints) are used to define and limit the value range and data integrity of columns in the table rule. By adding constraints to columns during table creation, you can ensure data correctness and consistency. This article will introduce the commonly used constraint types in MySQL and provide specific code examples.

1. PRIMARY KEY (primary key constraint)
   The primary key is a unique identifier for a column or a group of columns in a table. They guarantee the uniqueness of each row in the table. Using the PRIMARY KEY constraint can ensure that there is only one primary key in a table and there cannot be duplicate values.
   The following is an example that demonstrates how to add a primary key constraint to a table:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

2. FOREIGN KEY (foreign key constraint)
   Foreign keys are used to establish between multiple tables relation. It is a column or set of columns that reference the primary key of another table to ensure data integrity. When a FOREIGN KEY constraint is defined on a foreign key column, MySQL will automatically check whether the inserted or updated value exists in the related table.
   The following is an example showing how to add a foreign key constraint to a table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

3. UNIQUE (unique constraint)
   The unique constraint ensures that a certain column in a table only accepts unique value. In some cases, you may want to add a UNIQUE constraint to a column to avoid duplicate values.
   The following is an example showing how to add a unique constraint to a table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(50) UNIQUE,
    department_id INT
);

4. CHECK (check constraint)
   CHECK constraint is used to customize the value range of columns in the table . By using CHECK constraints, you can ensure that only data that meets specific conditions can be inserted into the table.
   The following is an example that demonstrates how to use CHECK constraints:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    quantity INT,
    price DECIMAL(10, 2),
    CONSTRAINT CHK_quantity CHECK (quantity >= 0),
    CONSTRAINT CHK_price CHECK (price > 0)
);

5. DEFAULT (default constraint)
   DEFAULT constraint is used to set a default value for a column so that when a new row is inserted , if no value is provided explicitly, the default value is automatically used.
   The following is an example showing how to add default constraints to a table:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50) DEFAULT '123456',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The above is an introduction to the commonly used constraint types in MySQL and how to use them. Using constraints ensures data integrity and consistency in your database. By using constraints appropriately, errors and inconsistent data can be reduced, and the efficiency and reliability of the database can be improved. I hope this article can help you understand and use the constraint function of MySQL.

The above is the detailed content of Detailed explanation of constraints in MySQL. For more information, please follow other related articles on the PHP Chinese website!