SQL Constraints

1. What are SQL Constraints?

SQL constraints restrict the type of data that can be stored in a column. They are applied while creating or altering tables.

• Maintain data integrity
• Enforce business rules
• Prevent invalid data entry

2. Types of SQL Constraints

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• DEFAULT
• CHECK

3. PRIMARY KEY

The PRIMARY KEY uniquely identifies each record in a table.

CREATE TABLE student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50)
);

• Must be unique
• Cannot contain NULL values
• Only one primary key per table

4. FOREIGN KEY

A FOREIGN KEY creates a link between two tables. It refers to the PRIMARY KEY of another table.

CREATE TABLE enroll (
    RollNo INT,
    CourseID INT,
    FOREIGN KEY (RollNo) REFERENCES student(RollNo)
);

• Maintains referential integrity
• Prevents orphan records

5. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

CREATE TABLE users (
    Email VARCHAR(100) UNIQUE
);

• No duplicate values allowed
• Multiple UNIQUE constraints allowed

6. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have NULL values.

CREATE TABLE employee (
    EmpName VARCHAR(50) NOT NULL
);

7. DEFAULT Constraint

The DEFAULT constraint assigns a default value when no value is provided.

CREATE TABLE orders (
    Status VARCHAR(20) DEFAULT 'Pending'
);

• Automatic value assignment
• Reduces NULL values

8. CHECK Constraint

The CHECK constraint ensures that values satisfy a specific condition.

CREATE TABLE voter (
    Age INT CHECK (Age >= 18)
);

• Validates data condition
• Enforces business rules

9. Adding Constraints using ALTER TABLE

ALTER TABLE student
ADD UNIQUE (Email);

10. Comparison of SQL Constraints

Constraint     Purpose
-------------  -----------------------------------
PRIMARY KEY    Unique + NOT NULL identifier
FOREIGN KEY    Links two tables
UNIQUE         Prevents duplicate values
NOT NULL       Disallows NULL values
DEFAULT        Sets default value
CHECK          Enforces condition

Practice Questions

1. What are SQL constraints?
2. Differentiate PRIMARY KEY and UNIQUE.
3. Explain FOREIGN KEY with example.
4. What is CHECK constraint?
5. How to add constraint using ALTER TABLE?

Practice Task