Constraints are rules applied to data columns in a table. They are used to limit the type of data that can go into a table, ensuring the accuracy and reliability of the data.
Common Constraints
| Constraint | Description |
|---|---|
NOT NULL | Ensures a column cannot have a NULL value |
UNIQUE | Ensures all values in a column are different |
PRIMARY KEY | Combination of NOT NULL and UNIQUE — uniquely identifies each row |
FOREIGN KEY | Links to a column in another table — enforces referential integrity |
CHECK | Ensures values satisfy a specific condition |
DEFAULT | Sets a default value when none is provided |
PRIMARY KEY
A primary key uniquely identifies each row in a table. It cannot contain NULLs and must be unique.
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100)
);Composite Primary Key
When a single column isn’t enough, use multiple columns together:
CREATE TABLE OrderItems (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);FOREIGN KEY
A foreign key links a column in one table to the primary key of another. It maintains referential integrity.
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);ON DELETE and ON UPDATE Actions
Define what happens when a parent row is deleted or updated:
| Action | Behaviour |
|---|---|
ON DELETE CASCADE | Deletes child rows when parent is deleted |
ON DELETE SET NULL | Sets foreign key to NULL when parent is deleted |
ON DELETE RESTRICT | Prevents deletion of parent if children exist |
ON DELETE NO ACTION | Similar to RESTRICT (checks at the end of transaction) |
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADECHECK Constraint
Enforces a condition on each row:
CREATE TABLE Products (
id INT PRIMARY KEY,
price DECIMAL(10,2) CHECK (price > 0),
stock INT DEFAULT 0
);Adding Constraints to Existing Tables
ALTER TABLE Employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);
ALTER TABLE Employees
ADD CONSTRAINT uq_email UNIQUE (email);Q: Difference between Primary Key and Unique Key?
A:
- Primary Key: A table can have only one Primary Key. It cannot contain NULL values.
- Unique Key: A table can have multiple Unique Keys. They can contain NULL values (usually only one NULL, depending on the DB).
Q: What is a Composite Primary Key?
A: A Primary Key that consists of two or more columns is called a composite Primary Key. It is used when a single column is not enough to uniquely identify a row.
Q: What does ON DELETE CASCADE do?
A: It is a property of a Foreign Key. If a record in the parent table is deleted, all corresponding records in the child table (the one containing the foreign key) will be automatically deleted.
Q: Can a FOREIGN KEY reference a non-PRIMARY KEY column?
A: Yes, but the referenced column must have a UNIQUE constraint on it. A foreign key can reference any candidate key (Primary Key or Unique).
1. Create table with composite primary key.
CREATE TABLE OrderItems (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);2. Add foreign key with ON DELETE CASCADE.
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);3. Create table with NOT NULL and DEFAULT.
CREATE TABLE Products (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
stock INT DEFAULT 0
);4. Design schema for Orders and Customers.
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);5. Add a CHECK constraint to an existing table.
ALTER TABLE Employees
ADD CONSTRAINT chk_age CHECK (age >= 18);6. Remove a constraint.
ALTER TABLE Employees DROP CONSTRAINT chk_age;Premium Content
Unlock Constraints & Keys and all premium lessons with a subscription.
From ₹199.99/year — See plans