DATABASE SYSTEMS LAB MANUAL
Keys & Constraints
Topic: Primary Key, Foreign Key, UNIQUE, NOT NULL, and Data Integrity
1. Objective:
By the end of this lab, you will be able to:
Understand the purpose of keys and constraints in relational databases.
Explain the concept of a Primary Key.
Explain the concept of a Foreign Key.
Understand the purpose of the UNIQUE constraint.
Understand the purpose of the NOT NULL constraint.
Understand entity integrity and referential integrity.
Create tables with appropriate constraints using SQL.
Implement Primary Key and Foreign Key relationships.
Apply UNIQUE and NOT NULL constraints.
Test valid and invalid data using MySQL Workbench.
Analyze constraint violations and explain why they occur.
2. HARDWARE AND SOFTWARE REQUIRED:
Hardware
PC or Laptop
Keyboard and Mouse
Minimum 4 GB RAM
Internet connection
Software
MySQL Server
MySQL Workbench
Windows / Linux / macOS
Other Requirements
Access to LMS
Previous Lab database knowledge
Folder for saving screenshots
Basic SQL knowledge
3. INTRODUCTION
In a relational database, data is stored in tables.
For example, a university database may contain:
Students
Courses
Departments
Teachers
1|Page
,DATABASE SYSTEMS LAB MANUAL
Enrollments
When data is stored in multiple tables, we need rules to make sure that the data remains:
Correct
Valid
Consistent
Unique where required
Properly connected between tables
These rules are implemented using keys and constraints.
4. WHAT IS A KEY?
A key is an attribute or a set of attributes used to identify records or establish relationships between
tables.
For example:
Student_ID Student_Name Program
101 Ali BSCS
102 Sara BSAI
103 Ahmed BSSE
Here, Student_ID can be used to uniquely identify each student.
5. PRIMARY KEY
A Primary Key is a column or combination of columns that uniquely identifies each record in a
table.
A Primary Key:
Must contain unique values.
Cannot contain NULL.
Identifies each record.
A table normally has one Primary Key definition.
Example:
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Program VARCHAR(50)
);
Here: “Student_ID” is the Primary Key.
Example Data
Student_ID Student_Name Program
2|Page
, DATABASE SYSTEMS LAB MANUAL
101 Ali BSCS
102 Sara BSAI
103 Ahmed BSSE
Every student has a different Student_ID.
6. WHY DO WE NEED A PRIMARY KEY?
Suppose a student table contains:
Student_ID Student_Name
101 Ali
101 Ahmed
Which student does ID 101 represent?
There is ambiguity.
A Primary Key prevents this situation by requiring every value to be unique.
7. FOREIGN KEY
A Foreign Key is a column that creates a relationship between two tables.
For example:
Students:
Student_ID Student_Name
101 Ali
102 Sara
Enrollments:
Enrollment_ID Student_ID Course
1 101 DBMS
2 102 OOP
The Student_ID in the Enrollments table refers to the Student_ID in the Students table.
Therefore:
Students.Student_ID
↑
Enrollments.Student_ID
This relationship can be implemented using a Foreign Key.
3|Page
Keys & Constraints
Topic: Primary Key, Foreign Key, UNIQUE, NOT NULL, and Data Integrity
1. Objective:
By the end of this lab, you will be able to:
Understand the purpose of keys and constraints in relational databases.
Explain the concept of a Primary Key.
Explain the concept of a Foreign Key.
Understand the purpose of the UNIQUE constraint.
Understand the purpose of the NOT NULL constraint.
Understand entity integrity and referential integrity.
Create tables with appropriate constraints using SQL.
Implement Primary Key and Foreign Key relationships.
Apply UNIQUE and NOT NULL constraints.
Test valid and invalid data using MySQL Workbench.
Analyze constraint violations and explain why they occur.
2. HARDWARE AND SOFTWARE REQUIRED:
Hardware
PC or Laptop
Keyboard and Mouse
Minimum 4 GB RAM
Internet connection
Software
MySQL Server
MySQL Workbench
Windows / Linux / macOS
Other Requirements
Access to LMS
Previous Lab database knowledge
Folder for saving screenshots
Basic SQL knowledge
3. INTRODUCTION
In a relational database, data is stored in tables.
For example, a university database may contain:
Students
Courses
Departments
Teachers
1|Page
,DATABASE SYSTEMS LAB MANUAL
Enrollments
When data is stored in multiple tables, we need rules to make sure that the data remains:
Correct
Valid
Consistent
Unique where required
Properly connected between tables
These rules are implemented using keys and constraints.
4. WHAT IS A KEY?
A key is an attribute or a set of attributes used to identify records or establish relationships between
tables.
For example:
Student_ID Student_Name Program
101 Ali BSCS
102 Sara BSAI
103 Ahmed BSSE
Here, Student_ID can be used to uniquely identify each student.
5. PRIMARY KEY
A Primary Key is a column or combination of columns that uniquely identifies each record in a
table.
A Primary Key:
Must contain unique values.
Cannot contain NULL.
Identifies each record.
A table normally has one Primary Key definition.
Example:
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Program VARCHAR(50)
);
Here: “Student_ID” is the Primary Key.
Example Data
Student_ID Student_Name Program
2|Page
, DATABASE SYSTEMS LAB MANUAL
101 Ali BSCS
102 Sara BSAI
103 Ahmed BSSE
Every student has a different Student_ID.
6. WHY DO WE NEED A PRIMARY KEY?
Suppose a student table contains:
Student_ID Student_Name
101 Ali
101 Ahmed
Which student does ID 101 represent?
There is ambiguity.
A Primary Key prevents this situation by requiring every value to be unique.
7. FOREIGN KEY
A Foreign Key is a column that creates a relationship between two tables.
For example:
Students:
Student_ID Student_Name
101 Ali
102 Sara
Enrollments:
Enrollment_ID Student_ID Course
1 101 DBMS
2 102 OOP
The Student_ID in the Enrollments table refers to the Student_ID in the Students table.
Therefore:
Students.Student_ID
↑
Enrollments.Student_ID
This relationship can be implemented using a Foreign Key.
3|Page
