SQL Primary Keys and Composite Keys: Design and Examples

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

A primary key expresses the table’s chosen row identity. Its values must be unique and present. Other tables can refer to that identity through foreign keys. A surrogate key such as a generated integer has no direct business meaning, while a natural key uses a meaningful domain identifier. A natural key can be suitable when its uniqueness and stability are genuinely guaranteed; a customer name usually has neither property.

A composite primary key combines columns. In an enrolment table, PRIMARY KEY(student_id,course_id) prevents the same student-course pair being stored twice. The student can still appear in different courses, and the course can still have many students. Neither column is individually unique. If repeated enrolments across terms are valid, term must be part of the identity or a different key policy is needed. The key must describe the real event rather than suppressing legitimate repetitions.

Key order can matter for the supporting index and lookup patterns even when the uniqueness combination is the same. A composite index starting with customer_id can commonly support that leading-column lookup more directly than a sale_id-only lookup. Physical indexing details differ, so do not turn this into a claim that every engine always stores primary keys the same way. Foreign keys referring to a composite identity must provide the corresponding column combination in the correct mapping.

A surrogate primary key does not eliminate business uniqueness needs. A table with id as primary key may still need UNIQUE(customer_id,tag) to prevent duplicate assignments. Conversely, using every descriptive field in a primary key creates a brittle identity that changes whenever a label is corrected. Choose the primary identity, add separate business constraints, and document the relationship each foreign key references. Prefer stable keys so updates do not require changing many referencing records.

Composite PRIMARY KEY syntax is supported by all four engines. Ordinary SQLite tables have historical cases in which some primary-key forms permit NULL unless NOT NULL, STRICT or WITHOUT ROWID rules address it. The example explicitly marks both components NOT NULL for portability rather than relying on an implicit assumption.

Check the engine-specific syntax and version requirements in SQLite CREATE TABLE primary keys.

Create a small enrolment table where the student-course pair is unique. Insert two courses for the same student and verify both.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

CREATE TABLE enrolments (
 student_id INTEGER NOT NULL,course_id INTEGER NOT NULL,
 PRIMARY KEY(student_id,course_id)
);
INSERT INTO enrolments (student_id,course_id) VALUES (1,10),(1,20);
SELECT student_id,course_id FROM enrolments ORDER BY student_id,course_id;

student_id | course_id
1 | 10
1 | 20

The two rows share student 1 but have different course ids, so their composite identities differ. Repeating (1,10) would be rejected. The table demonstrates pair uniqueness without inventing a requirement that either component must be globally unique.

Try inserting (1,newsletter) again into customer_tags and expect a key violation. Then insert a new tag for customer 1 and an existing tag for a different customer; both are valid new pairs. For an enrolment model with repeat terms, ask whether student-course alone suppresses legitimate events and revise the identity before adding more sample rows.