PRIMARY KEY and SERIAL behavior in PostgreSQL

Introduction

A PRIMARY KEY uniquely identifies each row in a table. SERIAL helps create automatic numbers for this key.

When you want to make sure each row in a table is unique.

When you need an automatic number for new rows, like an ID.

When you want to link tables using a unique identifier.

When you want to avoid manually typing unique IDs.

When you want the database to handle numbering automatically.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name SERIAL PRIMARY KEY,
  other_column datatype
);

SERIAL creates an integer column that auto-increments.

PRIMARY KEY means no two rows can have the same value in this column.

Examples

This creates a users table with an auto-incrementing user_id as the primary key.

PostgreSQL

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);

Each product gets a unique product_id automatically.

PostgreSQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name TEXT
);

order_number will automatically increase for each new order.

PostgreSQL

CREATE TABLE orders (
  order_number SERIAL PRIMARY KEY,
  order_date DATE
);

Sample Program

This creates an employees table with an auto-incrementing employee_id as the primary key. It inserts two employees and shows their IDs.

PostgreSQL

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  employee_name VARCHAR(100)
);

INSERT INTO employees (employee_name) VALUES ('Alice');
INSERT INTO employees (employee_name) VALUES ('Bob');

SELECT * FROM employees ORDER BY employee_id;

Important Notes

Time complexity: Inserting a row with SERIAL is fast and automatic.

Space complexity: SERIAL uses an integer, so it uses little space.

Common mistake: Forgetting to set PRIMARY KEY means duplicates can happen.

Use SERIAL when you want automatic numbering; use manual keys if you need custom IDs.

Summary

PRIMARY KEY makes sure each row is unique.

SERIAL creates automatic numbers for keys.

Together, they help manage unique IDs easily and safely.