Primary Key and Foreign Key
Primary Key and Foreign Key are fundamental concepts in relational databases used to uniquely identify records and establish relationships between tables.
What is a Primary Key?
A Primary Key is a column (or a combination of columns) that uniquely identifies each row in a table.
- It must contain unique values.
- It cannot contain
NULL. - Each table should have one primary key.
Example: In a students table, the student_id column can be the primary key.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);What is a Foreign Key?
A Foreign Key is a column in one table that refers to the Primary Key in another table. It is used to establish a relationship between the two tables.
- Helps maintain referential integrity.
- Allows linking of related data across tables.
Example: In a marks table, student_id can be a foreign key referencing students(student_id).
CREATE TABLE marks (
id INT PRIMARY KEY,
student_id INT,
subject VARCHAR(50),
score INT,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);Visual Relationship Example
students Table
| student_id (PK) | name |
|---|---|
| 1 | Amit |
| 2 | Priya |
marks Table
| id | student_id (FK) | subject | score |
|---|---|---|---|
| 1 | 1 | Math | 90 |
| 2 | 2 | Science | 85 |
