SQL Constraints
What
is a Constraint?
A Constraint is a rule
applied to a table column to ensure that only valid and accurate data is
stored in the database.
Simple
Definition
A constraint is a rule that controls
what data can be inserted, updated, or deleted in a table.
It helps maintain data accuracy,
consistency, and integrity.
Why Do We Use Constraints?
Without constraints, users can enter
incorrect or duplicate data.
Example
(Without Constraints)
|
StudentID |
Name |
Email |
|
101 |
Rahul |
rahul@gmail.com |
|
101 |
Amit |
amit@gmail.com |
❌ Duplicate StudentID
Example
(With Constraints)
|
StudentID |
Name |
Email |
|
101 |
Rahul |
rahul@gmail.com |
Trying to insert StudentID 101
again will produce an error.
✅ Correct and reliable data.
---------------------------------------------------------------------------------------------------------
There are two types of Constraints
1) I/O Constraints (Input/Output Constraints)
a.
PK, FK, Unique, Not Null
2)
Business Rule Constraints
a. Check
Types of SQL Constraints
There are 5 commonly used
constraints:
1.
NOT NULL
2.
UNIQUE
3.
PRIMARY KEY
4.
FOREIGN KEY
5.
CHECK
1. NOT NULL Constraint
Definition
A column cannot contain NULL
(empty) values.
Practical
Example
CREATE TABLE Student
(
StudentID NUMBER,
StudentName
VARCHAR2(30) NOT NULL
);
Valid
INSERT INTO Student
VALUES(101,'Rahul');
Invalid
INSERT INTO Student
VALUES(102,NULL);
Output
ERROR:
Cannot insert NULL into StudentName.
Real-Life
Example
Every student must have a name.
2. UNIQUE Constraint
Definition
A UNIQUE constraint does not
allow duplicate values.
Practical
Example
CREATE TABLE Student
(
StudentID NUMBER,
Email VARCHAR2(50)
UNIQUE
);
Valid
INSERT INTO Student
VALUES(101,'rahul@gmail.com');
Invalid
INSERT INTO Student
VALUES(102,'rahul@gmail.com');
Output
ERROR:
Duplicate value not allowed.
Real-Life
Example
Two students cannot have the same
email address.
3. PRIMARY KEY Constraint
Definition
A PRIMARY KEY uniquely identifies
each record in a table.
It is a combination of:
- NOT NULL
- UNIQUE
Practical
Example
CREATE TABLE Student
(
StudentID NUMBER
PRIMARY KEY,
StudentName
VARCHAR2(30)
);
Valid
INSERT INTO Student
VALUES(101,'Rahul');
Invalid
(Duplicate)
INSERT INTO Student
VALUES(101,'Amit');
Invalid
(NULL)
INSERT INTO Student
VALUES(NULL,'Rahul');
Output
ERROR:
Primary Key cannot be NULL or duplicate.
Real-Life
Example
Every student has a unique
Enrollment Number.
4. FOREIGN KEY Constraint
Definition
A FOREIGN KEY creates a relationship
between two tables.
The value in the child table must
already exist in the parent table.
Practical
Example
Parent
Table
CREATE TABLE Department
(
DeptID NUMBER
PRIMARY KEY,
DeptName
VARCHAR2(30)
);
Insert Data
INSERT INTO Department VALUES(1,'Computer');
INSERT INTO Department VALUES(2,'Commerce');
Child
Table
CREATE TABLE Student
(
StudentID NUMBER
PRIMARY KEY,
StudentName
VARCHAR2(30),
DeptID NUMBER,
FOREIGN KEY
(DeptID)
REFERENCES
Department(DeptID)
);
Valid
INSERT INTO Student
VALUES(101,'Rahul',1);
Invalid
INSERT INTO Student
VALUES(102,'Amit',10);
Output
ERROR:
Department does not exist.
Real-Life
Example
A student cannot be assigned to a
department that does not exist.
5. CHECK Constraint
Definition
A CHECK constraint allows only
values that satisfy a specified condition.
Practical
Example
CREATE TABLE Student
(
StudentID NUMBER,
Age NUMBER
CHECK(Age>=18)
);
Valid
INSERT INTO Student
VALUES(101,20);
Invalid
INSERT INTO Student
VALUES(102,15);
Output
ERROR:
CHECK constraint violated.
Real-Life
Example
Only students 18 years or older
are eligible for admission.
How to Define Constraints?
Constraints can be defined in two
ways.
1.
Column-Level Constraint
Constraint is written immediately
after the column definition.
CREATE TABLE Student
(
StudentID NUMBER
PRIMARY KEY,
StudentName
VARCHAR2(30) NOT NULL,
Email VARCHAR2(50)
UNIQUE
);
2.
Table-Level Constraint
Constraint is written after all
column definitions.
CREATE TABLE Student
(
StudentID NUMBER,
StudentName
VARCHAR2(30),
Email
VARCHAR2(50),
PRIMARY
KEY(StudentID),
UNIQUE(Email)
);
Complete Practical Example
CREATE TABLE Student
(
StudentID NUMBER
PRIMARY KEY,
StudentName
VARCHAR2(30) NOT NULL,
Email VARCHAR2(50)
UNIQUE,
Age NUMBER
CHECK(Age>=18),
DeptID NUMBER,
FOREIGN
KEY(DeptID)
REFERENCES
Department(DeptID)
);
Sample Data
Valid
Record
INSERT INTO Student
VALUES
(101,'Rahul','rahul@gmail.com',20,1);
Output
1 row inserted.
Invalid
Record
INSERT INTO Student
VALUES
(101,NULL,'rahul@gmail.com',15,10);
Errors
- Duplicate StudentID ❌
- Name is NULL ❌
- Age less than 18 ❌
- Department does not exist ❌
Comparison of SQL Constraints
|
Constraint |
Purpose |
Allows NULL? |
Allows Duplicate? |
Example |
|
NOT NULL |
Prevent empty values |
❌ No |
✔ Yes |
Student Name |
|
UNIQUE |
Prevent duplicate values |
✔ Yes (one or more NULLs depending on DBMS) |
❌ No |
Email |
|
PRIMARY KEY |
Unique identification |
❌ No |
❌ No |
Student ID |
|
FOREIGN KEY |
Link two tables |
✔ Yes (unless also NOT NULL) |
✔ Yes |
Department ID |
|
CHECK |
Restrict values using a condition |
✔ Yes (unless also NOT NULL) |
✔ Yes |
Age ≥ 18 |
Real-Life Examples
|
Constraint |
Real-Life Example |
|
NOT NULL |
Every student must have a name. |
|
UNIQUE |
Every student has a unique email ID. |
|
PRIMARY KEY |
Every student has a unique enrollment number. |
|
FOREIGN KEY |
Every student belongs to an existing department. |
|
CHECK |
Employee salary must be greater than 0. |
Summary
- NOT NULL → Ensures a column cannot be empty.
- UNIQUE → Prevents duplicate values in a column.
- PRIMARY KEY → Uniquely identifies each record (combines NOT NULL and UNIQUE).
- FOREIGN KEY → Maintains relationships between tables by referencing a parent table.
- CHECK
→ Allows only values that satisfy a specified condition.
Using these constraints helps keep
the database accurate, consistent, and reliable.
Or follow my blog from the below link
){kind=link}
0 Comments