Single Primary Key vs Composite Primary Key, which one is a better choose?

A simple guide to database design

There are many discussions and arguments around to decide what is best in case of primary key in relational databases design. Let’s discuss both the advantages and disadvantages of single primary key and composite primary key, and the best use cases of each.

Single Primary Key

Single primary key is a easiest way to identify uniqueness of rows of a table. When the table has one column for unique identity.

Let’s look at a simple example:

First create a table collecting student information:

CREATE TABLE student
(id TEXT,
name TEXT,
age INTEGER,
class TEXT);

insert some dummy data:

INSERT INTO student VALUES
(1, 'Zoe', '23', 'Math'),
(2, 'Lou', '20', 'Chinese'),
(3, 'Lily', '21', 'English'),
(4, 'Joe', '25', 'Math'),
(5, 'Sarah', '19', 'Psychology'),
(6, 'Yong', '20', 'Chemistry'),
(7, 'Hao Wang', '23', 'Math');

We can see there is no primary key set for now, so we can insert any duplicated values.

Let’s assign id as primary key:

ALTER TABLE student
ADD CONSTRAINT student_id_pk
PRIMARY KEY (id);