How to Set a Primary Key in SQL

Use a primary key when each row in a table must have one unique, non-null identifier. This is the foundation of reliable joins, updates, deletes, and data integrity.

What you’ll build or solve

You’ll learn how to set a primary key in SQL during table creation and on existing tables. You’ll also know how to choose a safe key column.

When this approach works best

This approach is the right choice when rows need a guaranteed unique identity.

Common real-world scenarios include:

• User IDs
• Order numbers
• Product IDs
• Enrollment records
• Audit event rows

This is a bad idea when the chosen column can change over time, like email or display name.

Prerequisites

You only need:

• Basic CREATE TABLE or ALTER TABLE knowledge
• A column whose values are unique and never NULL

Step-by-step instructions

Step 1: Define the primary key in CREATE TABLE

The cleanest approach is setting it when the table is created.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(255)
);

This guarantees unique user_id values.

You can also add it later.

ALTER TABLE users
ADD PRIMARY KEY (user_id);

This works only if existing values are unique and non-null.

What to look for:

• One primary key per table
• Values must be unique
• Values cannot be NULL
• Great for joins and foreign keys
• Usually best on stable numeric IDs

Examples you can copy

Orders table

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  total DECIMAL(10,2)
);

Existing products table

ALTER TABLE products
ADD PRIMARY KEY (product_id);

Composite key

PRIMARY KEY (student_id, course_id)

Common mistakes and how to fix them

Mistake 1: Using a changeable business field

What the reader might do:

PRIMARY KEY (email)

Why it breaks: emails can change.

Corrected approach:

Use a stable surrogate ID.

Mistake 2: Adding a primary key to duplicate data

What the reader might do:

ALTER TABLE users
ADD PRIMARY KEY (user_id);

Why it breaks: duplicates or NULL values make the statement fail.

Corrected approach:

Clean duplicates and nulls first.

Mistake 3: Using multiple single-column primary keys

What the reader might do:

Try defining two separate primary keys.

Why it breaks: a table can have only one primary key definition.

Corrected approach:

Use one composite primary key if needed.

Troubleshooting

If the statement fails, check for duplicates.

If NULL values exist, clean them first.

If the chosen field may change, use an ID column instead.

If multiple columns define uniqueness, use a composite key.

Quick recap

• Primary keys uniquely identify rows
• Values must be unique and non-null
• Best set during table creation
• Use stable ID columns
• Use composite keys for multi-column uniqueness