PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. It ensures that the column(s) defined as the primary key are both unique and not null.

A table can have only one primary key, which can consist of a single column or a combination of columns (composite key).

Syntax

CREATE TABLE table_name (
  column_name datatype PRIMARY KEY
);

For composite primary key:

CREATE TABLE table_name (
  col1 datatype,
  col2 datatype,
  PRIMARY KEY (col1, col2)
);

Or add to an existing table:

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Example using employees table

Let’s define the id column as the primary key in the employees table:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50),
  gender VARCHAR(50)
);

INSERT INTO employees (id, name, age, department, gender) VALUES
(1, 'Alice', 28, 'HR', 'female'),
(2, 'Bob', 32, 'IT', 'male'),
(3, 'John', 25, 'Sales', 'male'),
(4, 'David', 30, 'Marketing', 'male');

-- This insert will fail because id 2 already exists
INSERT INTO employees (id, name, age, department, gender)
VALUES (2, 'Duplicate Bob', 29, 'Finance', 'male'); -- ❌ Error!

The last insert fails because id = 2 already exists, and id is defined as the primary key.

Key Points

  • Automatically implies NOT NULL and UNIQUE
  • A table can have only one primary key
  • Often used as a reference in other tables via FOREIGN KEY

Next, we’ll explore the FOREIGN KEY constraint, which links data across tables.