SQL Primary Key | Academic Block

SQL PRIMARY KEY

Learn how to use the SQL PRIMARY KEY constraint to uniquely identify every record in a database table.

What is a PRIMARY KEY?

A PRIMARY KEY is a column or combination of columns that uniquely identifies each row in a table. A primary key cannot contain duplicate values, and it cannot contain NULL values.

Primary keys are commonly used to identify customers, products, employees, orders, students, and other records in a database.

Basic PRIMARY KEY Syntax

A primary key can be defined directly when creating a table.

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT,
  course TEXT
);

PRIMARY KEY with INTEGER

In SQLite, an INTEGER PRIMARY KEY column can automatically receive an integer value when a new row is inserted without specifying the primary key.

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL
);

INSERT INTO books (title, author) VALUES
  ('The Silent Planet', 'Riya Sen'),
  ('Beyond the Horizon', 'Arjun Rao'),
  ('Digital Dreams', 'Meera Kapoor');

SELECT *
FROM books
ORDER BY book_id;

PRIMARY KEY with Manually Assigned Values

You can also explicitly provide the primary key value when inserting records, as long as each value is unique.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL
);

INSERT INTO products (product_id, product_name, price) VALUES
  (501, 'Wireless Speaker', 2499.00),
  (502, 'Desk Lamp', 1299.00),
  (503, 'Travel Charger', 899.00);

SELECT *
FROM products
ORDER BY product_id;

PRIMARY KEY Prevents Duplicate IDs

A primary key must be unique. Attempting to insert another row with an existing primary key value causes a constraint error.

The following example demonstrates the valid records before the duplicate operation. The duplicate insertion is intentionally not included so that the example remains fully executable without errors.

DROP TABLE IF EXISTS inventory;

CREATE TABLE inventory (
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  quantity INTEGER
);

INSERT INTO inventory VALUES
  (1, 'Steel Bolts', 120),
  (2, 'Copper Wire', 75),
  (3, 'Aluminum Sheets', 40);

SELECT *
FROM inventory
ORDER BY item_id;

PRIMARY KEY with Other Constraints

A primary key can be used together with other constraints such as NOT NULL, UNIQUE, CHECK, and DEFAULT.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  email TEXT UNIQUE,
  age INTEGER CHECK (age >= 18),
  status TEXT DEFAULT 'Active'
);

INSERT INTO employees (
  employee_name,
  email,
  age
) VALUES
  ('Aarav', 'aarav@example.com', 28),
  ('Nisha', 'nisha@example.com', 31),
  ('Kabir', 'kabir@example.com', 25);

SELECT *
FROM employees
ORDER BY employee_id;

PRIMARY KEY and FOREIGN KEY

A primary key is often referenced by a foreign key in another table. This creates a relationship between the two tables.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_name) VALUES
  ('Rohan'),
  ('Priya'),
  ('Sameer');

INSERT INTO orders (
  customer_id,
  product_name
) VALUES
  (1, 'Laptop Stand'),
  (2, 'Wireless Mouse'),
  (1, 'USB Hub');

SELECT
  orders.order_id,
  customers.customer_name,
  orders.product_name
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id
ORDER BY orders.order_id;

Composite PRIMARY KEY

A composite primary key consists of two or more columns. The combination of values must be unique, even though an individual column may contain duplicate values.

DROP TABLE IF EXISTS enrollments;

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  enrollment_date TEXT,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments VALUES
  (1, 101, '2026-08-10'),
  (1, 102, '2026-08-11'),
  (2, 101, '2026-08-12'),
  (3, 103, '2026-08-13');

SELECT *
FROM enrollments
ORDER BY student_id, course_id;

Checking the PRIMARY KEY Definition

SQLite provides PRAGMA table_info() for inspecting table columns. The pk field indicates whether a column is part of the primary key.

DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name TEXT NOT NULL,
  location TEXT
);

SELECT
  cid,
  name,
  type,
  pk
FROM pragma_table_info('departments')
ORDER BY cid;

PRIMARY KEY with AUTOINCREMENT

SQLite can use AUTOINCREMENT with an INTEGER PRIMARY KEY. It ensures automatically generated row IDs are not reused from previously deleted rows.

DROP TABLE IF EXISTS tickets;

CREATE TABLE tickets (
  ticket_id INTEGER PRIMARY KEY AUTOINCREMENT,
  subject TEXT NOT NULL
);

INSERT INTO tickets (subject) VALUES
  ('Login issue'),
  ('Payment question'),
  ('Profile update');

SELECT *
FROM tickets
ORDER BY ticket_id;

PRIMARY KEY vs UNIQUE

Feature	PRIMARY KEY	UNIQUE
Duplicate values	Not allowed	Not allowed
NULL values	Not allowed	Depends on database behavior
Number per table	One primary key definition	Multiple UNIQUE constraints possible
Main purpose	Identify each row	Prevent duplicate values

Common PRIMARY KEY Uses

Table	Primary Key	Purpose
Customers	customer_id	Identifies each customer
Products	product_id	Identifies each product
Orders	order_id	Identifies each order
Students	student_id	Identifies each student

Important Points

A primary key uniquely identifies every row in a table.
A table can have only one primary key definition.
A primary key cannot contain duplicate values.
A primary key cannot contain NULL values.
A primary key can consist of one column or multiple columns.
Primary keys are commonly referenced by foreign keys.
SQLite supports INTEGER PRIMARY KEY for automatically assigned row IDs.

Best Practices

Choose a stable and unique column for the primary key.
Use an integer ID when a simple surrogate key is appropriate.
Keep primary key values stable after records are created.
Use composite primary keys when uniqueness depends on multiple columns.
Use foreign keys to reference primary keys when establishing table relationships.
Avoid using frequently changing business information as a primary key.

The PRIMARY KEY constraint is one of the most important components of a relational database. It provides a reliable way to uniquely identify records and forms the foundation for relationships between database tables.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.