Constraints in MySQL

Constraints in MySQL are rules enforced on data in a database to ensure its accuracy, integrity, and consistency. They are applied at the table column or table level to restrict the type of data that can be stored. Constraints help prevent invalid data entry and maintain relational database integrity.

---

Types of Constraints in MySQL

1. NOT NULL Constraint

• Ensures that a column cannot have a NULL value.
• Used to ensure that important fields always contain data.

Syntax:

CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL
);

Example:

• If you try to insert a record with a NULL value in the name column, it will result in an error:

INSERT INTO students (id, name) VALUES (1, NULL); -- This will throw an error

---

2. UNIQUE Constraint

• Ensures that all the values in a column are distinct (no duplicates allowed).
• Multiple UNIQUE constraints can be applied to a table.

Syntax:

CREATE TABLE employees (
    emp_id INT UNIQUE,
    email VARCHAR(100) UNIQUE
);

Example:

• The email column must have unique values:

INSERT INTO employees (emp_id, email) VALUES (1, 'test@example.com');
INSERT INTO employees (emp_id, email) VALUES (2, 'test@example.com'); -- Error: Duplicate entry

---

3. PRIMARY KEY Constraint

• A combination of NOT NULL and UNIQUE.
• Ensures each row in a table has a unique and non-NULL identifier.
• Only one primary key is allowed per table.

Syntax:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL
);

Example:

• The order_id column will uniquely identify each record:

INSERT INTO orders (order_id, order_date) VALUES (1, '2024-01-01');
INSERT INTO orders (order_id, order_date) VALUES (1, '2024-01-02'); -- Error: Duplicate entry

---

4. FOREIGN KEY Constraint

• Ensures a relationship between columns in two tables.
• The foreign key in one table references a primary key in another table, ensuring referential integrity.

Syntax:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50)
);

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

Example:

• If a record in the orders table references a customer_id not present in the customers table, it will throw an error:

INSERT INTO orders (order_id, customer_id) VALUES (1, 100); -- Error if customer_id 100 doesn't exist

---

5. CHECK Constraint

• Ensures that the values in a column meet a specific condition.
• Supported from MySQL 8.0.16 onwards.

Syntax:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2),
    CHECK (price > 0)
);

Example:

• Ensures the price is greater than 0:

INSERT INTO products (product_id, price) VALUES (1, -10.00); -- Error: CHECK constraint failed

---

6. DEFAULT Constraint

• Assigns a default value to a column if no value is specified during insertion.

Syntax:

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2) DEFAULT 0.00
);

Example:

• If no value is provided for the balance column, it will default to 0.00:

INSERT INTO accounts (account_id) VALUES (1);
SELECT * FROM accounts; -- balance will show 0.00

---

Modifying Constraints

1. Adding Constraints

Constraints can be added to an existing table using the ALTER TABLE statement.

Example:

ALTER TABLE students
ADD CONSTRAINT unique_name UNIQUE (name);

2. Dropping Constraints

Constraints can be removed using the ALTER TABLE statement.

Example:

ALTER TABLE students
DROP INDEX unique_name; -- For UNIQUE

---

Key Points to Remember

1. Constraints help maintain data integrity and consistency.
2. NOT NULL, UNIQUE, PRIMARY KEY, and DEFAULT are column-level constraints.
3. FOREIGN KEY and CHECK can be both column-level and table-level constraints.
4. A PRIMARY KEY implicitly creates a UNIQUE constraint.
5. Always ensure proper planning of constraints to avoid invalid data entry.

---

Practical Use Case

Example Scenario:

A database for an e-commerce platform:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    password VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0)
);

This ensures:

1. user_id in users is unique and non-null.
2. Each order must belong to a valid user.
3. Product prices cannot be zero or negative.