What Is a Primary Key in SQL? Learn with Examples

A primary key is a column in a database table that uniquely identifies each row. That means no two rows can have the same value in this column, and it can’t be left empty.

It’s the most important way to organize your data and avoid duplicates.

Each table can have only ONE primary key.
It can be just one column or a few columns joined together to make each row unique.

Why Is It Important?

The primary key is essential for a few key reasons:

• It makes sure each row is unique
• It helps the database work faster when searching or linking data
• It allows other tables to connect to this one through foreign keys
• It prevents duplicate or missing records

Without a primary key, there’s no reliable way to know which row is which.

Example: Primary Key with Countries and Cities

Let’s look at a simple example using two tables: one for countries and one for cities.

Table 1: Countries

Each country has a short code (like "US" or "CH") that is unique. That code is used as the primary key.

CREATE TABLE Countries (
  country_code CHAR(2) PRIMARY KEY,
  name VARCHAR(100),
  continent VARCHAR(50)
);

In this case, country_code is the primary key. It identifies each country uniquely.

Table 2: Cities

Now let’s say you want to store information about cities. Each city will belong to a country, and we’ll include a country_code column so we know where the city is located.

CREATE TABLE Cities (
  city_id INT PRIMARY KEY,
  name VARCHAR(100),
  population INT,
  country_code CHAR(2)
);

Here, city_id is the primary key for the Cities table. Each city gets a unique ID number, like 1, 2, 3, and so on.

Later, we can use the country_code in the Cities table to create a foreign key that connects each city to a real country, but that only works if the Countries table has a clear primary key defined.

If you want to learn more about how foreign keys work, check out this article: What Is a Foreign Key

What Makes a Good Primary Key?

There are two main rules:

1. It must be unique - no two rows can have the same value

2. It must not be null - every row must have a value

If you try to insert a duplicate or missing value, the database will return an error.

Composite Primary Keys

Sometimes a single column isn’t enough to uniquely identify a row. In that case, you can use a composite primary key, which is made of two or more columns.

Here’s an example:

CREATE TABLE CarRegistrations (
  country_code CHAR(2),
  plate_number VARCHAR(10),
  registration_date DATE,
  PRIMARY KEY (country_code, plate_number)
);

In this case, a car’s license plate isn’t unique on its own - the same plate could exist in multiple countries. But the combination of country_code and plate_number is unique, so it makes a perfect composite primary key. This structure ensures no duplicates and keeps everything clearly linked.

Adding a Primary Key Later

You can also define a primary key after creating the table, using ALTER TABLE:

ALTER TABLE Countries
ADD PRIMARY KEY (country_code);

This is useful if you forget to define it at the beginning or need to apply it to existing data.

How to Work with Primary Keys in DbSchema

If you’re using DbSchema, you can define primary keys without writing any SQL.

Here’s how:

1. Create or import your table
2. Double-click on the column you want to set as the primary key
3. Click “Set as Primary Key”
4. You’ll see a small key icon on that column in the diagram

DbSchema also supports composite keys. Just select multiple columns when defining the key.

Working visually helps you understand your design faster, especially when working with multiple tables.

Summary

• A primary key uniquely identifies each row in a table
• It must be unique and NOT NULL
• Other tables can link to it using foreign keys
• You can define it during table creation or later using ALTER TABLE
• Visual tools like DbSchema make it easy to design and understand primary keys

Understanding primary keys is the first step toward building well-structured, reliable databases.