SQL Constraints in MySQL | PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT and CHECK

A database should store only accurate, valid, and meaningful data. To ensure data integrity and maintain consistency, MySQL provides SQL Constraints. Constraints are rules applied to table columns that restrict the type of data that can be inserted, updated, or deleted.

For example, a student should not have two different roll numbers, an admission number should never be left blank, and a marks column should not contain text values. Such rules are enforced using SQL constraints.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the purpose of SQL Constraints.
• Identify different types of constraints in MySQL.
• Create tables using constraints.
• Maintain data integrity in relational databases.
• Differentiate between PRIMARY KEY and FOREIGN KEY.

---

What are SQL Constraints?

---

Why Do We Need Constraints?

Without Constraints	With Constraints
Duplicate records may be stored.	Duplicate data is prevented wherever required.
Important fields may remain empty.	Mandatory fields cannot be left blank.
Relationships between tables may become invalid.	Relationships remain consistent.
Database becomes unreliable.	Data remains accurate and trustworthy.

---

Types of SQL Constraints

Constraint	Purpose
NOT NULL	Does not allow NULL values.
UNIQUE	Allows only unique values.
PRIMARY KEY	Uniquely identifies each record.
FOREIGN KEY	Maintains relationships between tables.
DEFAULT	Assigns a default value if none is provided.
CHECK	Allows only values satisfying a specified condition.

---

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values. Every record must provide a value for that column.

Syntax

CREATE TABLE Student
(
    Roll_No INT,
    Name VARCHAR(50) NOT NULL
);

Example

In this table, every student must have a name. MySQL will not allow a record with a NULL value in the Name column.

---

UNIQUE Constraint

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

Syntax

CREATE TABLE Student
(
    Admission_No INT UNIQUE,
    Name VARCHAR(50)
);

Example

Each student receives a unique admission number. Two students cannot have the same admission number.

---

PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies every record in a table. A primary key column cannot contain duplicate values or NULL values.

Syntax

CREATE TABLE Student
(
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(10)
);

---

Characteristics of PRIMARY KEY

• Uniquely identifies every record.
• Cannot contain NULL values.
• Cannot contain duplicate values.
• Only one PRIMARY KEY is allowed in a table.

---

Difference Between PRIMARY KEY and UNIQUE

PRIMARY KEY	UNIQUE
Does not allow NULL values.	Can allow NULL values (implementation dependent).
Only one PRIMARY KEY per table.	Multiple UNIQUE constraints can exist.
Uniquely identifies each record.	Prevents duplicate values.

---

Sample Student Table

Roll_No (PK)	Name	Class
101	Aarav	XI
102	Diya	XI
103	Kabir	XI
104	Riya	XII

---

---

FOREIGN KEY Constraint

A FOREIGN KEY is a column (or a group of columns) in one table that refers to the PRIMARY KEY of another table. It establishes a relationship between the two tables and helps maintain referential integrity.

Syntax

CREATE TABLE Result
(
    Roll_No INT,
    Subject VARCHAR(50),
    Marks INT,
    FOREIGN KEY (Roll_No)
    REFERENCES Student(Roll_No)
);

---

Example

Suppose the Student table contains the roll numbers of all students. The Result table stores marks obtained by students. The Roll_No column in the Result table acts as a FOREIGN KEY and refers to the PRIMARY KEY in the Student table.

Student Table

Roll_No (PK)	Name
101	Aarav
102	Diya
103	Kabir

Result Table

Roll_No (FK)	Subject	Marks
101	Computer Science	91
102	Computer Science	84
103	Computer Science	88

Since the Roll_No values already exist in the Student table, the relationship between both tables remains valid.

---

DEFAULT Constraint

The DEFAULT constraint automatically inserts a predefined value into a column if no value is provided during record insertion.

Syntax

CREATE TABLE Student
(
    Roll_No INT,
    City VARCHAR(30) DEFAULT 'Jaipur'
);

Example

If the city is not specified while inserting a record, MySQL automatically stores 'Jaipur' as the value.

---

CHECK Constraint

The CHECK constraint ensures that the values entered into a column satisfy a specified condition.

Syntax

CREATE TABLE Student
(
    Roll_No INT,
    Marks INT CHECK (Marks >= 0 AND Marks <= 100)
);

Example

The above constraint ensures that marks entered for any student remain between 0 and 100.

---

Comparison of SQL Constraints

Constraint	Main Purpose	Example
NOT NULL	Mandatory value	Name cannot be NULL.
UNIQUE	No duplicate values	Admission Number
PRIMARY KEY	Unique identification	Roll Number
FOREIGN KEY	Maintains relationship between tables	Result.Roll_No
DEFAULT	Stores a predefined value	City = Jaipur
CHECK	Validates data	Marks between 0 and 100

---

Solved Example 1

Create a Student table where Roll_No is the PRIMARY KEY.

Solution:

CREATE TABLE Student
(
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(10)
);

---

Solved Example 2

Create an Employee table where Email must be unique.

Solution:

CREATE TABLE Employee
(
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

---

Solved Example 3

Create a Student table where the Name column cannot contain NULL values.

Solution:

CREATE TABLE Student
(
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

---

Solved Example 4

Create a Result table where Roll_No is a FOREIGN KEY referencing the Student table.

Solution:

CREATE TABLE Result
(
    Roll_No INT,
    Subject VARCHAR(50),
    Marks INT,
    FOREIGN KEY (Roll_No)
    REFERENCES Student(Roll_No)
);

---

Solved Example 5

Create a Student table where the default city is Jaipur.

Solution:

CREATE TABLE Student
(
    Roll_No INT,
    Name VARCHAR(50),
    City VARCHAR(30) DEFAULT 'Jaipur'
);

---

Real-Life Applications

Organization	Application	Constraint Used
School	Unique Roll Number	PRIMARY KEY
School	Link Result with Student	FOREIGN KEY
Hospital	Patient ID	PRIMARY KEY
Bank	Unique Account Number	UNIQUE
Library	Book ID	PRIMARY KEY
Online Store	Default Product Status	DEFAULT

---

Common Errors

Mistake	Correct Practice
Creating duplicate PRIMARY KEY values.	PRIMARY KEY values must always be unique.
Leaving a NOT NULL field empty.	Always provide a value for mandatory columns.
Using a FOREIGN KEY value that does not exist in the parent table.	Ensure the referenced value exists in the parent table.
Expecting DEFAULT to override a supplied value.	DEFAULT is used only when no value is provided.
Entering values outside the CHECK condition.	Ensure the entered values satisfy the CHECK constraint.

---

Interview Corner

---

---

Competency-Based Questions

1. A school wants to ensure that every student has a unique Roll Number and that no Roll Number is left blank. Which constraint should be used? Write the SQL statement.
2. Write an SQL query to create a Student table where the Name column cannot contain NULL values and the Admission_No column must contain unique values.
3. A school stores student information in the Student table and examination results in the Result table. Explain why a FOREIGN KEY is required.
4. Write an SQL statement to create an Employee table where the default city is 'Jaipur'.
5. Differentiate between PRIMARY KEY and FOREIGN KEY with suitable examples.

---

Multiple Choice Questions

1. SQL Constraints are used to:
   • (a) Delete records
   • (b) Maintain data accuracy and integrity
   • (c) Create databases only
   • (d) Display reports
2. Which constraint prevents NULL values in a column?
   • (a) UNIQUE
   • (b) NOT NULL
   • (c) CHECK
   • (d) DEFAULT
3. Which constraint ensures that duplicate values are not allowed?
   • (a) DEFAULT
   • (b) UNIQUE
   • (c) CHECK
   • (d) FOREIGN KEY
4. Which constraint uniquely identifies each record in a table?
   • (a) UNIQUE
   • (b) NOT NULL
   • (c) PRIMARY KEY
   • (d) DEFAULT
5. Which constraint establishes a relationship between two tables?
   • (a) PRIMARY KEY
   • (b) FOREIGN KEY
   • (c) CHECK
   • (d) UNIQUE
6. The DEFAULT constraint is used to:
   • (a) Prevent duplicate values
   • (b) Insert a predefined value if no value is supplied
   • (c) Delete duplicate records
   • (d) Create relationships
7. The CHECK constraint is used to:
   • (a) Sort records
   • (b) Create indexes
   • (c) Restrict values according to a condition
   • (d) Join two tables
8. How many PRIMARY KEY constraints can a table have?
   • (a) Unlimited
   • (b) Two
   • (c) One
   • (d) Five
9. Which statement about a PRIMARY KEY is correct?
   • (a) It allows duplicate values.
   • (b) It allows NULL values.
   • (c) It uniquely identifies every record.
   • (d) It is used only for sorting.
10. Which SQL statement correctly creates a PRIMARY KEY?
    • (a) Roll_No UNIQUE PRIMARY
    • (b) Roll_No INT PRIMARY KEY
    • (c) PRIMARY Roll_No
    • (d) Roll_No KEY PRIMARY

---

Quick Revision

Constraint	Purpose
NOT NULL	Does not allow NULL values.
UNIQUE	Prevents duplicate values.
PRIMARY KEY	Uniquely identifies each record.
FOREIGN KEY	Maintains relationships between tables.
DEFAULT	Assigns a default value automatically.
CHECK	Allows only values satisfying a specified condition.

---

Important Points to Remember

• Constraints improve the accuracy, consistency, and reliability of data stored in a database.
• NOT NULL ensures that a column always contains a value.
• UNIQUE prevents duplicate values in a column.
• PRIMARY KEY uniquely identifies each record and does not allow NULL or duplicate values.
• FOREIGN KEY establishes a relationship between two tables and maintains referential integrity.
• DEFAULT assigns a predefined value when no value is supplied during insertion.
• CHECK validates data by allowing only values that satisfy a specified condition.
• A table can have only one PRIMARY KEY but may contain multiple UNIQUE constraints.

---

CBSE Exam Tips

• Understand the purpose of each constraint instead of memorizing only the syntax.
• Do not confuse PRIMARY KEY with UNIQUE or FOREIGN KEY.
• Practice writing CREATE TABLE statements using different constraints.
• Remember that a FOREIGN KEY always refers to the PRIMARY KEY of another table.
• Revise the characteristics of each constraint, especially PRIMARY KEY and FOREIGN KEY.
• Expect competency-based questions involving school management systems, banking, hospitals, libraries, and online shopping databases.

---

Summary

SQL Constraints are essential for maintaining the accuracy, consistency, and integrity of data in a relational database. Constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, and CHECK ensure that only valid and meaningful data is stored. They prevent duplicate records, enforce relationships between tables, restrict invalid values, and automatically assign default values when required. A good understanding of SQL Constraints is fundamental for designing reliable databases and solving CBSE Class 11 Informatics Practices examination questions.

---

Chapter Complete - Database Query using SQL

---

What's Next?

The next unit in the CBSE Class 11 Informatics Practices (2026–27) syllabus is Introduction to Emerging Trends. In this unit, you will explore modern technologies such as Artificial Intelligence (AI), Machine Learning (ML), Cloud Computing, Big Data, Internet of Things (IoT), and other emerging technologies that are transforming the world.