SQL Primary Key: Candidate Keys, Composite Keys & Best Practices

In the relational model, the Primary Key (PK) is the fundamental cornerstone of entity identity. A primary key is an attribute (or combination of attributes) that uniquely distinguishes every individual tuple in a table. It strictly enforces two mathematical guarantees: Uniqueness and Non-Nullability.

Figure 2.1: Referential integrity model showing Primary Key in parent table and Foreign Key linkage in child table.

Key Definitions (BCA / CS University Theory)

• Super Key: Any set of one or more columns that taken together uniquely identify a row within a table.

• Candidate Key: A minimal super key (a super key with no redundant attributes). A table may have multiple candidate keys (e.g., student_id, aadhaar_number, email).

• Primary Key: The specific candidate key chosen by the database architect as the primary identifier for the relation.

• Alternate Key: Candidate keys that were not selected as the primary key.

1. Single-Column Primary Key

-- Column-level definition
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- Or using AUTO_INCREMENT surrogate key
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    CONSTRAINT pk_course PRIMARY KEY (course_id)
);

2. Composite Primary Key (Multi-Column)

When an entity's uniqueness depends on the combination of two or more columns, define a Composite Primary Key at the table level. A classic example is a university course enrollment table:

CREATE TABLE course_enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade VARCHAR(2),
    
    -- Composite Primary Key: A student can enroll in a specific course only once!
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

Surrogate Key vs Natural Key: Which Should You Use?

Now let us see how primary keys link tables together via SQL Foreign Key!

Frequently Asked Questions (FAQ)