? MySQL Constraints

? Quick Overview

Constraints in MySQL are used to specify rules for data in a table. They are used to ensure the integrity of data in the database. Constraints enforce the accuracy and reliability of the data in a table.

? Key Concepts

NOT NULL – Ensures that a column cannot have a NULL value.
UNIQUE – Ensures that all values in a column are unique.
PRIMARY KEY – Uniquely identifies each record.
FOREIGN KEY – Maintains relationships between tables.
CHECK – Validates column values.
DEFAULT – Assigns default values.

? Syntax / Theory

Constraints can be defined during table creation or added later. They are enforced automatically by MySQL before data is inserted or updated.

? Code Example: Table with Constraints

? View Code Example

-- Creating a users table with multiple constraints
CREATE TABLE users (
id INT AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
password VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
CHECK (LENGTH(password) > 6)
);

? Explanation

This table ensures unique users, non-empty values, and validates password length automatically.

? Code Example: Foreign Key

? View Code Example

-- Linking orders table with users table using foreign key
CREATE TABLE orders (
order_id INT AUTO_INCREMENT,
user_id INT,
order_date DATE,
PRIMARY KEY (order_id),
FOREIGN KEY (user_id) REFERENCES users(id)
);

? Explanation

The foreign key ensures every order is linked to a valid user, maintaining referential integrity.

? Interactive Concept

users.id ➜ orders.user_id

This relationship visually represents how foreign keys connect tables.

? Use Cases

Prevent duplicate records.
Maintain table relationships.
Validate business rules.
Ensure consistent default values.

✅ Tips & Best Practices

Always define a primary key.
Use foreign keys for relationships.
Apply CHECK constraints for validation.
Use DEFAULT for predictable data.

? Try It Yourself

Create a products table with constraints.
Add a CHECK constraint on price.
Use DEFAULT for quantity.
Link categories using foreign keys.