Keys in SQL
In a database, data is stored in tables, and each table contains multiple records.
To identify records uniquely and maintain relationships between tables, SQL uses a concept called Keys.
Keys play a crucial role in:
- Ensuring data uniqueness
- Maintaining data accuracy
- Establishing relationships between tables
- Preventing duplicate records
Understanding SQL keys is essential for designing reliable and well structured databases.
Example: Primary Key
Here, student_id uniquely identifies each student.
Why Do We Need Keys in SQL?
Without keys:
- Duplicate records can exist
- Data inconsistency may occur
- Table relationships cannot be enforced
Keys help the database:
- Identify each record uniquely
- Link related data across tables
- Enforce data integrity rules
Types of Keys in SQL
In this chapter, we will learn the following SQL keys:
- Primary Key
- Foreign Key
- Candidate Key
- Composite Key
1. Primary Key
A Primary Key is a column (or combination of columns) that uniquely identifies each record in a table.
Key Characteristics:
- Values must be unique
- Values cannot be NULL
- Only one primary key per table
- Often used to identify records internally
Example: Primary Key
CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50), marks INT);Here, student_id uniquely identifies each student.
2. Foreign Key
A Foreign Key is a column that creates a relationship between two tables.
It refers to the Primary Key of another table.
Example
Student Table
Marks Table
Example: Foreign Key
This ensures that student_id in marks must exist in the students table.
Example
Student Table
| student_id | name |
|---|---|
| 1 | Rahul |
Marks Table
| mark_id | student_id | marks |
|---|---|---|
| 101 | 1 | 82 |
Example: Foreign Key
CREATE TABLE marks ( mark_id INT PRIMARY KEY, student_id INT, marks INT, FOREIGN KEY (student_id) REFERENCES students(student_id));This ensures that student_id in marks must exist in the students table.
3. Candidate Key
A Candidate Key is a column (or set of columns) that can uniquely identify records in a table.
From all candidate keys, one is chosen as the Primary Key.
Example: Candidate Key
Example: Composite Key
Here, neither column alone is unique, but together they uniquely identify a record.
Example: Candidate Key
In a table:
- student_id
Both can uniquely identify a student.
- All are candidate keys
- One is selected as the primary key
4. Composite Key
A Composite Key is a key that consists of two or more columns to uniquely identify a record.
It is used when a single column is not sufficient to ensure uniqueness.
Example: Composite Key
CREATE TABLE attendance ( student_id INT, subject_id INT, PRIMARY KEY (student_id, subject_id));Here, neither column alone is unique, but together they uniquely identify a record.
Two Minute Drill
- Keys uniquely identify records
- Primary Key - unique and NOT NULL
- Foreign Key - links two tables
- Candidate Key - possible primary keys
- Composite Key - multiple columns as key
- Keys maintain data integrity