In database design, constraints are rules used to ensure the integrity, accuracy and consistency of data. MySQL provides a variety of constraint types to help us regulate data storage and ensure that it complies with expected rules. Common constraints include primary key, foreign key, unique constraint, non-null constraint, default value, and check constraint.
1. Primary Key Constraint
effect:A primary key is a column used to uniquely identify data in a table. It not only requires that the column's value is unique, but cannot be empty (i.e. NULL is not allowed). Each table can only have one primary key.
Features:
- Uniqueness: Each value in the primary key column must be unique.
- NULL values are not allowed: The primary key column cannot contain NULL values.
- Automatic indexing: MySQL will automatically create indexes for primary key columns to optimize query performance.
Example:
CREATE TABLE employees (
employee_id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
PRIMARY KEY (employee_id)
);
In the above example, employee_id is the primary key, which uniquely identifies each employee record and cannot be NULL.
2. Foreign Key Constraints
effect:Foreign key constraints are used to establish a connection relationship between two tables, ensuring that a column of data in one table must match the primary or unique key data in another table. Foreign keys are used to maintain reference integrity of data.
Features:
- The value of the foreign key column must be consistent with the primary key or unique column value in the reference table.
- Foreign key constraints can define cascading operations during update or deletion, such as: ON DELETE CASCADE or ON UPDATE CASCADE.
Example:
CREATE TABLE departments (
department_id INT NOT NULL,
department_name VARCHAR(50),
PRIMARY KEY (department_id)
);
CREATE TABLE employees (
employee_id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
PRIMARY KEY (employee_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
In this example, the department_id column in the employees table is a foreign key pointing to the department_id column in the departments table. This ensures that each employee must be affiliated with a certain department.
3. Unique constraints (Unique)
effect:Unique constraints ensure that all values in a column are unique and no duplication is allowed. Unlike primary keys, a unique constraint allows NULL values to exist in a column, but multiple NULL values can exist.
Features:
- Ensure that all non-NULL values in the column are unique.
- Allows multiple NULL values (which may vary across database systems).
Example:
CREATE TABLE users (
user_id INT NOT NULL,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
PRIMARY KEY (user_id)
);
In the table above, the username and email columns have unique constraints, ensuring that no two users use the same username or mailbox.
4. Not Null
effect:The non-null constraint ensures that the data in the column cannot be NULL. When the NOT NULL constraint is defined, a valid value must be provided for the column when inserting data.
Features:
- Mandatory columns must have values.
- Commonly used for fields with special requirements for business logic.
Example：
CREATE TABLE products (
product_id INT NOT NULL,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2),
PRIMARY KEY (product_id)
);
In this example, the product_name column cannot be NULL and each product must have a name.
5. Default value constraints (Default)
effect:The default value constraint is used to specify a default value for a column when inserting data. MySQL uses the default value when the column's value is not provided when data is inserted.
Features:
- Provides a column's default value.
- If the column value is not specified, the database uses the default value.
Example：
CREATE TABLE orders (
order_id INT NOT NULL,
order_date DATE DEFAULT CURRENT_DATE,
status VARCHAR(20) DEFAULT 'Pending',
PRIMARY KEY (order_id)
);
In this example, the default value of the order_date column is the current date, while the default value of the status column is ‘Pending’.
6. Check constraints (Check)
effect:Check constraints are used to restrict the data in a column that must meet a certain condition. It ensures that the inserted data complies with specific rules. This constraint is supported in MySQL 8.0.16 and later.
Features:
- Force the data in the column to meet the specified criteria.
- Complex conditions can be defined using logical expressions.
Example:
CREATE TABLE employees (
employee_id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
salary DECIMAL(10, 2),
PRIMARY KEY (employee_id),
CHECK (age >= 18),
CHECK (salary >= 0)
);
In this table, the value of the age column must be greater than or equal to 18, and the value of the salary column must be greater than or equal to 0.
7. Logical and physical connectivity
In databases, logical connectivity and physical connectivity are important concepts in database design:
Logical connectivity refers to the association relationship established between tables through foreign key constraints. These associations define how data is logically organized and constrained.
Physical connectivity Focuses on the association of database tables on physical storage. For example, a database can use indexes to improve query efficiency, especially under primary keys and unique constraints, where MySQL automatically creates indexes for these columns.
Example:
CREATE TABLE products (
product_id INT NOT NULL,
product_name VARCHAR(100),
PRIMARY KEY (product_id)
);
CREATE TABLE order_items (
order_item_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT,
PRIMARY KEY (order_item_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
In the above example, the order_items table establishes a logical connection with the products table through the product_id foreign key. Physically, MySQL may create indexes for the product_id column to speed up queries.
8. Modify constraints (ALTER TABLE)
If you need to modify the constraints of the table after creating a table, you can use the ALTER TABLE statement to add, delete, or modify the constraints.
Example: Add constraints
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65);
Example: Delete Constraints
ALTER TABLE employees DROP CONSTRAINT chk_age;
9. Comprehensive example: Create a complete order system
Combined with the previous constraints, here is a simple order system design example, which contains primary keys, foreign keys, unique constraints, non-empty constraints, default values, and check constraints:
CREATE TABLE customers (
customer_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15),
PRIMARY KEY (customer_id)
);
CREATE TABLE products (
product_id INT NOT NULL,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (product_id)
);
CREATE TABLE orders (
order_id INT NOT NULL,
customer_id INT NOT NULL,
order_date DATE DEFAULT CURRENT_DATE,
status VARCHAR(20) DEFAULT 'Pending',
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_item_id INT NOT NULL,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT CHECK (quantity > 0),
PRIMARY KEY (order_item_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
In this system:
- The customers table stores customer information, and the email column has unique constraints to ensure that each customer's email address is unique.
- The products table stores product information, and each product has a unique product_id.
- The orders table records customer orders, customer_id is a foreign key, pointing to the customers table.
- The order_items table records the items for each order, including the foreign keys order_id and product_id, and sets check constraints for the quantity column to ensure that the quantity of items for each line item is greater than 0.
Conclusion
By using constraints in MySQL rationally, data integrity, correctness and consistency can be ensured. When designing a database, constraints not only prevent the insertion of wrong data, but also improve the efficiency of data query and operation. Mastering the use of these constraints will help us build higher quality database systems in actual projects.
The above is the detailed content of the summary of common constraints of MySQL data tables. For more information about MySQL data table constraints, please pay attention to my other related articles!