6.1 Constraints (NOT NULL, UNIQUE, PK, FK, CHECK, DEFAULT)

Level: 6 | Version: v1.1 | Author: Meptrasoft

Overview

SQL constraints are rules applied to table columns to help keep data valid, consistent, and reliable.

The six common constraints covered here are:

Constraints help the database prevent invalid data from being stored.

NOT NULL

NOT NULL ensures that a column cannot contain a NULL value.

Example:

name VARCHAR(100) NOT NULL

This means a student must have a value in the name column.

NOT NULL → Value is required

UNIQUE

UNIQUE prevents duplicate values for the constrained column or column combination.

Example:

email VARCHAR(150) UNIQUE

Two students should not have the same email address under this constraint.

UNIQUE → Prevent duplicate values

Note: NULL handling for UNIQUE constraints can vary depending on the database system.

PRIMARY KEY

A PRIMARY KEY uniquely identifies each row in a table.

Example:

student_id INT PRIMARY KEY

A primary key must be unique and cannot be NULL.

student_id
101 → Arjun
102 → Meena
103 → Ravi

PRIMARY KEY → Unique identity of a row

A table has one primary key constraint, which can consist of one column or multiple columns.

FOREIGN KEY

A FOREIGN KEY creates a relationship between tables by referencing a key in another table.

Example:

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(100),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id)
);

Here:

students.student_id
        ↑
        │
enrollments.student_id

The foreign key helps prevent a child row from referencing a parent key that does not exist, subject to the configured foreign-key actions.

FOREIGN KEY → Connect related tables

CHECK

CHECK requires a value to satisfy a specified condition.

Example:

age INT CHECK (age > 0)

This prevents values that violate the condition.

For example:

Age = 22  ✓
Age = 0   ✗
Age = -5  ✗

CHECK → Value must satisfy a rule

DEFAULT

DEFAULT provides a value when an INSERT does not supply one for that column.

Example:

status VARCHAR(20) DEFAULT 'ACTIVE'

Then:

INSERT INTO students (name, email, age)
VALUES ('Arjun', 'arjun@gmail.com', 22);

The database can automatically use:

status = ACTIVE

DEFAULT → Use a value automatically when none is supplied

Practice Table Setup

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT CHECK (age > 0),
    status VARCHAR(20) DEFAULT 'ACTIVE'
);

INSERT INTO students (name, email, age)
VALUES
('Arjun', 'arjun@gmail.com', 22),
('Meena', 'meena@gmail.com', 21);

This table demonstrates:

student_id → PRIMARY KEY
name       → NOT NULL
email      → UNIQUE
age        → CHECK
status     → DEFAULT

FOREIGN KEY Example

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_name VARCHAR(100)
);

Here enrollments.student_id references students.student_id.

A student ID that does not exist in students cannot normally be inserted into enrollments.

Constraint Comparison

Easy Memory Trick

NOT NULL    → REQUIRED
UNIQUE      → NO DUPLICATES
PRIMARY KEY → IDENTIFY
FOREIGN KEY → RELATE
CHECK       → RULE
DEFAULT     → AUTOMATIC VALUE

Constraints with ALTER TABLE

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

For example:

ALTER TABLE students
ADD CONSTRAINT chk_age
CHECK (age > 0);

The exact syntax can vary between database systems.

Common Beginner Mistakes

Placement Quick Points

NOT NULL
→ REQUIRED VALUE

UNIQUE
→ NO DUPLICATE VALUES

PRIMARY KEY
→ UNIQUE ROW IDENTIFIER

FOREIGN KEY
→ TABLE RELATIONSHIP

CHECK
→ VALIDATION RULE

DEFAULT
→ AUTOMATIC VALUE

• Constraints help maintain data integrity.
• NOT NULL prevents NULL values.
• UNIQUE enforces uniqueness for the constrained key.
• PRIMARY KEY uniquely identifies rows.
• FOREIGN KEY maintains relationships between tables.
• CHECK enforces a condition.
• DEFAULT supplies a value when one is not provided.

Interview Questions

What is a SQL constraint?

A constraint is a rule applied to database data to help maintain validity and integrity.

What is a FOREIGN KEY?

A foreign key is a column or set of columns that references a key in another table to enforce a relationship.

What is the purpose of CHECK?

CHECK ensures that values satisfy a specified condition.

What does DEFAULT do?

DEFAULT supplies a value when an INSERT does not provide one for that column.

Can a PRIMARY KEY contain NULL?

No.

Why are constraints important?

They allow the database itself to enforce important data rules instead of relying only on application code.

Can constraints be added after table creation?

Yes. Many database systems allow constraints to be added or modified using ALTER TABLE, although exact syntax varies.

Practice & Hands-On Exercises

Using the students and enrollments tables:

1. Identify each constraint in the students table.
2. Try inserting a student without a name.
3. Try inserting a duplicate email:

INSERT INTO students (name, email, age)
VALUES ('Duplicate User', 'arjun@gmail.com', 25);

4. Try inserting an invalid age:

INSERT INTO students (name, email, age)
VALUES ('Invalid Age', 'invalid@gmail.com', -5);

5. Insert a student without status and observe the default value:

INSERT INTO students (name, email, age)
VALUES ('New Student', 'new@gmail.com', 20);
SELECT * FROM students WHERE email = 'new@gmail.com';

6. Try inserting an enrollment with a non-existing student_id:

INSERT INTO enrollments (student_id, course_name)
VALUES (999, 'Machine Learning');

7. Explain the difference between PRIMARY KEY and FOREIGN KEY.
8. Explain the difference between UNIQUE and NOT NULL.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

NOT NULL     → REQUIRED
UNIQUE       → NO DUPLICATES
PRIMARY KEY  → IDENTIFY
FOREIGN KEY  → RELATE
CHECK        → VALIDATE
DEFAULT      → FILL AUTOMATICALLY

SQL constraints are database rules that help keep data valid, unique, consistent, and correctly related.

End of Constraints (NOT NULL, UNIQUE, PK, FK, CHECK, DEFAULT)