SQL - Primary Key

SQL Primary Key

What is a SQL Primary Key?

A SQL Primary Key is a constraint that uniquely identifies each record in a database table. It’s the unique identifier for a row, much like a Social Security Number is for a person or a VIN is for a car. This key ensures that you can reliably and uniquely reference any specific row in the table.

Beyond just identification, primary keys are crucial for database performance. They automatically create an index, which significantly speeds up data retrieval operations. They also form the backbone of relationships between tables, which you’ll see when we discuss Foreign Keys.

Key Properties of a Primary Key

A Primary Key is fundamentally a combination of two other constraints:

• UNIQUE: All values in the primary key column(s) must be different. No two rows can have the same primary key value.
• NOT NULL: The primary key column cannot have NULL values. Every row must have a primary key value.
• Single Key Per Table: A table can have only one Primary Key, though this key can consist of one or more columns (this is called a composite key).

Choosing a Primary Key: Natural vs. Surrogate

When choosing a primary key, you have two main options:

• Natural Key: A key that is formed from an attribute that already exists in the real world and is unique. For example, a user’s email address or a country’s ISO code. While seemingly convenient, natural keys can be problematic if they change (e.g., a user changes their email address).
• Surrogate Key: A key that has no business meaning and is created solely to be the primary key. This is usually an auto-incrementing integer or a universally unique identifier (UUID). Surrogate keys are the recommended best practice for most scenarios as they are stable and guaranteed to be unique within the table.

Creating a Primary Key

During Table Creation (Recommended)

The best time to define a primary key is when you first create the table. The syntax varies slightly across different database systems for auto-incrementing surrogate keys.

Syntax: PostgreSQL (using SERIAL)

CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Syntax: MySQL (using AUTO_INCREMENT)

CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Syntax: SQL Server (using IDENTITY)

CREATE TABLE Customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) UNIQUE NOT NULL
);

You can also define the constraint separately, which is useful for naming the constraint and for creating composite keys.

CREATE TABLE Customers (
    customer_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

On an Existing Table

If a table was created without a primary key, you can add one using the ALTER TABLE statement. This will only succeed if the column(s) you choose already contain unique, non-null values.

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

Example

Let’s say we have a Products table without a PK.

-- This will only work if 'product_sku' has unique, non-null values.
ALTER TABLE Products
ADD CONSTRAINT pk_products PRIMARY KEY (product_sku);

Composite Primary Keys

When a single column isn’t enough to uniquely identify a row, you can use a composite primary key, which consists of two or more columns. A classic example is a linking table in a many-to-many relationship.

Example

Imagine a table that links orders to products. The combination of order_id and product_id must be unique.

CREATE TABLE Order_Items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    -- Foreign key constraints would also be added here
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

Dropping a Primary Key

Dropping a primary key is uncommon but possible with ALTER TABLE. If the constraint was explicitly named, you must use that name. If not, the syntax might be database-specific.

Syntax (with a named constraint)

ALTER TABLE Customers
DROP CONSTRAINT pk_customers;

Syntax (MySQL without a named constraint)

ALTER TABLE Customers
DROP PRIMARY KEY;

Warning: Dropping a primary key can have serious consequences, especially if other tables reference it via foreign keys. It will also remove the performance benefit of the associated index.

Common Errors and Best Practices

• Error: ‘Duplicate entry’ or ‘UNIQUE constraint failed’. This happens when you try to insert a row with a primary key value that already exists.
• Error: ‘Column cannot be null’. This occurs when you try to insert a row without providing a value for the primary key column.
• Best Practice: Prefer surrogate keys (like SERIAL or AUTO_INCREMENT) over natural keys.
• Best Practice: Always name your constraints (CONSTRAINT pk_customers PRIMARY KEY(...)). This makes managing and debugging them much easier in the future.