SQL Constraints in DBMS (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL)
SQL Constraints in DBMS (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL)
SQL Constraints are rules applied to table columns to ensure the accuracy, consistency, and integrity of data in a database. Constraints help maintain reliable data and prevent invalid entries in relational database systems.
In this lesson, you will learn different types of SQL constraints and how they are used in real-world database design.
What are SQL Constraints?
SQL constraints are restrictions applied to columns in a table to enforce rules on the data being inserted or updated. They play a critical role in maintaining data integrity.
Constraints are widely used in relational databases like MySQL, PostgreSQL, and Microsoft SQL Server.
Types of SQL Constraints
1. PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a table.
- Cannot contain NULL values
- Must contain unique values
Example:
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50)
);
2. FOREIGN KEY
The FOREIGN KEY constraint is used to establish a relationship between two tables.
- References primary key of another table
- Ensures referential integrity
Example:
CREATE TABLE Orders (
Order_ID INT,
Student_ID INT,
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID)
);
3. UNIQUE
The UNIQUE constraint ensures that all values in a column are different.
Example:
Email VARCHAR(100) UNIQUE
4. NOT NULL
The NOT NULL constraint ensures that a column cannot have NULL values.
Example:
Name VARCHAR(50) NOT NULL
5. CHECK
The CHECK constraint ensures that values meet a specific condition.
Example:
Age INT CHECK (Age >= 18)
Real-World Example
Table: Students
- Student_ID (Primary Key)
- Email (UNIQUE)
- Name (NOT NULL)
Table: Courses
- Course_ID (Primary Key)
Table: Enrollment
- Student_ID (Foreign Key)
Constraints ensure:
- No duplicate records
- Proper relationships between tables
- Valid and consistent data
Why SQL Constraints are Important
SQL constraints help you:
- Maintain data accuracy
- Enforce business rules
- Prevent invalid data entry
- Improve database reliability
FAQs
What are SQL constraints?
SQL constraints are rules that ensure data accuracy and integrity in a database.
What is PRIMARY KEY constraint?
It uniquely identifies each record in a table.
What is FOREIGN KEY?
It links two tables and maintains relationships.
Why use NOT NULL constraint?
It ensures that a column cannot store empty values.
Where can I learn more courses like this?
Click here for more free courses