What are the commonly used constraints in mysql?

MySQL provides a variety of constraints to ensure data integrity, including: primary key constraints (uniquely identify records), foreign key constraints (records between associated tables), unique constraints (ensure that a field or field combination is unique ), check constraints (define data value range), NOT NULL constraints (prevent null values), auto-increment constraints (automatically generate unique numbers), default constraints (specify field default values), foreign key reference constraints (ensure that foreign key values ​​refer to the main table records), index constraints (to improve query performance).

Commonly used constraints in MySQL

MySQL provides a series of constraints to ensure data integrity and consistency sex and effectiveness. The following are the most commonly used constraints:

1. Primary key constraints

• uniquely identify each record in the table.
• Can not be empty.
• usually consists of a unique value or a combination of values.

2. Foreign key constraints

• Associate a field in one table with a primary key column in another table.
• Ensure data consistency and prevent "suspended" records.

3. Unique constraints

• Ensure that each record in the table is unique on the specified field or field combination.
• Null values ​​are allowed.

4. Check constraints

• Define what values ​​the data can take in the table.
• You can use SQL expressions for verification.

5. NOT NULL constraint

• Ensure that the specified field cannot contain null values.

6. Auto-increment constraint

• Automatically generate a unique and increasing number for newly inserted records in the table.

7. Default constraint

• Specifies the default value of the field. When inserting a record, if the field value is empty, the default value is used.

8. Foreign key reference constraints

• Specifies that the foreign key field must reference a record that exists in the main table.

9. Index constraints

• are not real constraints, but they can improve query performance on the table.
• Allows fast and efficient searches on specified fields or combinations of fields.

Example:

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE,
  price DECIMAL(10,2) CHECK (price > 0),
  category_id INT,
  CONSTRAINT FK_category FOREIGN KEY (category_id) REFERENCES categories (id)
);

In this example, the following constraints are used:

• Primary key constraint (id)
• Unique constraint (name)
• Check constraint (price)
• NOT NULL constraint (id, name, price)
• Auto-increment constraint (id)
• Foreign key constraints (FK_category)

The above is the detailed content of What are the commonly used constraints in mysql?. For more information, please follow other related articles on the PHP Chinese website!