Introduction
A table needs a reliable way to distinguish one record from another. Names, departments, and other descriptive values can repeat, so they may not identify a particular row accurately.
A primary key provides that identity. It ensures that every row has a unique, non-null key value, making it possible to identify records precisely and reference them from related tables.
What Is a Primary Key?
A primary key is the chosen column or combination of columns that uniquely identifies each row in a table. It enforces entity integrity, meaning that each record has a unique identity and no part of its primary key is NULL.
Consider a students table:
student_id | student_name | department |
|---|---|---|
101 | Asha | CSE |
102 | Ravi | ECE |
103 | Asha | ME |
The name Asha appears twice, so student_name cannot uniquely identify these records. Each student_id is different, making it suitable as the primary key.
The main properties are:
Unique values: No two rows can have the same complete primary key value.
No NULL values: Every column forming the primary key must contain a value.
One primary key per table: That key may contain one column or several columns.
Minimal identification: In relational design, a primary key is a chosen candidate key, with no unnecessary columns.
A primary key does not need to be numeric or automatically generated. A suitable character code can also identify records.
Creating a Primary Key in SQL
Use the PRIMARY KEY constraint when creating a table. The following example uses MySQL-compatible SQL:
CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, department VARCHAR(20));Here, student_id is the primary key. The database rejects an insert that duplicates an existing identifier or explicitly supplies NULL for it.
INSERT INTO students (student_id, student_name, department)VALUES (101, 'Asha', 'CSE'), (102, 'Ravi', 'ECE');An attempt to insert another student with student_id = 101 fails because that value already identifies a row.
You can also declare the key as a table-level constraint:
CREATE TABLE students ( student_id INT, student_name VARCHAR(100) NOT NULL, department VARCHAR(20), PRIMARY KEY (student_id));These are alternative definitions of the same table, not statements to execute consecutively.
Composite Primary Key
A composite primary key contains more than one column. The combination must be unique, although individual column values may repeat.
For example, an enrollment table can identify each enrollment using both a student identifier and a course identifier:
CREATE TABLE enrollments ( student_id INT, course_id INT, enrollment_date DATE, PRIMARY KEY (student_id, course_id));The following records satisfy this key:
student_id | course_id | enrollment_date |
|---|---|---|
101 | 10 | 2026-08-01 |
101 | 20 | 2026-08-02 |
102 | 10 | 2026-08-01 |
Student 101 can join multiple courses, and course 10 can have multiple students. However, the pair (101, 10) cannot appear again.
Neither key column can contain NULL. This design assumes a student can enroll in a particular course only once; if repeat enrollments are allowed across terms, the key must account for that requirement.
Primary Key
Adding a Primary Key to an Existing Table
If a table was created without a primary key, add one using ALTER TABLE:
ALTER TABLE studentsADD PRIMARY KEY (student_id);This example assumes that students does not already have a primary key. Existing values must satisfy the key requirements before the constraint can be added.
Check for duplicate identifiers:
SELECT student_id, COUNT(*) AS occurrencesFROM studentsGROUP BY student_idHAVING COUNT(*) > 1;This command groups records by student_id, counts each identifier’s occurrences, and returns only identifiers appearing more than once.
If the query returns no rows, no duplicate identifiers were found. This is an empty result, not a NULL value.
Also check for null identifiers:
SELECT *FROM studentsWHERE student_id IS NULL;Resolve duplicate or missing identifiers before adding the key. Assigning a primary key does not automatically decide which existing records should be changed or removed.
Primary Key vs Other Keys
A table may have several possible ways to identify records. The primary key is the one selected as the table’s main identifier.
Key | Purpose | Example |
|---|---|---|
Super key | Any set of attributes that uniquely identifies a row, possibly with extra attributes |
|
Candidate key | A minimal set of attributes that uniquely identifies a row |
|
Primary key | The candidate key chosen as the main identifier | Selected |
Alternate key | A candidate key not selected as the primary key | A required, unique institutional email |
Foreign key | Enforces a reference to a key in another or the same table |
|
A UNIQUE constraint can enforce another identifier, but it is not automatically equivalent to a primary key. In MySQL, a nullable unique column permits multiple NULL values, while a primary key permits none.
Primary keys also do not create relationships by themselves. The referencing table needs a foreign key constraint to enforce the relationship.
Choosing a Good Primary Key
Choose a key that identifies records reliably throughout their lifetime. A value can be unique today yet still be a poor choice if it changes frequently or can later be reassigned.
Prefer stable values: A student identifier is usually more dependable than a name or phone number.
Keep the key minimal: Include only the columns needed to identify a row.
Use a suitable data type: Choose enough range or length for expected records.
Consider business rules: A composite key should reflect what makes a record unique.
Separate generation from enforcement: Automatic number generation supplies values; the primary key constraint enforces their uniqueness and non-nullability.
A natural key comes from meaningful business data, such as a course code. A surrogate key is introduced specifically for identification, such as a generated numeric ID. Either can be suitable when its rules match the table’s needs.
Summary
A primary key gives each table row a unique, non-null identity. It can consist of one column or several columns, but a table has only one primary key constraint.
Choose a stable, minimal identifier and ensure existing data satisfies its rules before adding the constraint. Distinguishing primary keys from candidate, unique, and foreign keys helps produce clearer table designs and reliable relationships.
Be the first to add a comment.