Keys in DBMS – Super Key, Candidate Key, Primary Key, Foreign Key

Detailed explanation with examples and Oracle SQL implementation.

1. Super Key

A super key is a set of one or more attributes that uniquely identifies a record in a table. It may contain extra attributes that are not necessary.

Example: Student Table

| student_id | name  | email          | phone      |
|------------|-------|----------------|-----------|
| 101        | Ahsan | a@example.com  | 1234567890 |

Possible super keys: {student_id}, {student_id, email}, {student_id, name}

2. Candidate Key

A candidate key is a minimal super key with no unnecessary attributes. A table can have multiple candidate keys.

Example: Candidate keys in Student Table: {student_id}, {email}

3. Primary Key

The primary key is one chosen candidate key that uniquely identifies table records. Must be unique and not null.

CREATE TABLE Student (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    email VARCHAR2(50) UNIQUE,
    phone VARCHAR2(15)
);

Here, student_id is the primary key.

4. Foreign Key

A foreign key is an attribute in one table that refers to the primary key in another table. Ensures referential integrity.

CREATE TABLE Course (
    course_id NUMBER PRIMARY KEY,
    course_name VARCHAR2(50)
);

CREATE TABLE Enrollment (
    student_id NUMBER,
    course_id NUMBER,
    PRIMARY KEY(student_id, course_id),
    FOREIGN KEY(student_id) REFERENCES Student(student_id),
    FOREIGN KEY(course_id) REFERENCES Course(course_id)
);

Here, Enrollment.student_id is a foreign key referencing Student, and Enrollment.course_id references Course.

5. Summary Table

Key Type	Definition	Example
Super Key	Set of attributes uniquely identifying a record	{student_id, email}
Candidate Key	Minimal super key with no extra attributes	{student_id}, {email}
Primary Key	Chosen candidate key for uniqueness	student_id
Foreign Key	Attribute referring to another table’s primary key	Enrollment.student_id → Student.student_id

Trust Coding : Programming

DBMS Keys – Super Key, Candidate Key, Primary Key, and Foreign Key with Oracle Examples