In MySQL, constraints are used to define and enforce rules for the data that is stored in tables. Constraints help ensure data integrity by preventing invalid or inappropriate data from being stored in the table. There are several types of constraints in MySQL, including:
1. NOT NULL Constraint: The NOT NULL constraint ensures that a column cannot have a NULL value. If a user tries to insert or update a row with a NULL value, an error will be thrown.
Example:
CREATE TABLE customers (
customer_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
address VARCHAR(100),
PRIMARY KEY (customer_id)
);2. UNIQUE Constraint: The UNIQUE constraint ensures that a column or combination of columns have unique values. This means that no two rows in the table can have the same value in the specified column(s).
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
email VARCHAR(50) UNIQUE,
phone VARCHAR(20) UNIQUE
);3. PRIMARY KEY Constraint: The PRIMARY KEY constraint ensures that each row in a table is uniquely identified by a specific column or combination of columns. This constraint is used to identify the primary key of a table.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);4. FOREIGN KEY Constraint: The FOREIGN KEY constraint ensures that data in a column matches the values of data in another table’s column. It establishes a relationship between two tables.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);5. CHECK Constraint: The CHECK constraint is used to ensure that data in a column meets a specific condition or set of conditions.
Example:
CREATE TABLE payments (
payment_id INT PRIMARY KEY,
customer_id INT,
payment_date DATE,
amount DECIMAL(10, 2),
CHECK (amount >= 0 AND amount <= 10000),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);All of these constraints help ensure that data integrity is maintained in a MySQL database. By enforcing rules for the data in table columns, constraints prevent invalid or inappropriate data from being stored in the table.