PRIMARY KEY constraint in SQL

Introduction

A PRIMARY KEY constraint makes sure each row in a table is unique and easy to find. It helps keep data organized and prevents duplicates.

When you want to identify each record in a table uniquely, like each student in a class.

When you need to link data between tables, like orders linked to customers.

When you want to prevent duplicate entries in a table.

When you want to quickly search for a specific row using a unique ID.

When you want to enforce data integrity by making sure no two rows have the same key.

Syntax

SQL

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

A PRIMARY KEY column cannot have NULL values.

You can only have one PRIMARY KEY per table, but it can be made of multiple columns (composite key).

Examples

Creates a Students table where StudentID uniquely identifies each student.

SQL

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

Creates a table with a composite primary key using OrderID and ProductID together.

SQL

CREATE TABLE Orders (
  OrderID INT,
  ProductID INT,
  PRIMARY KEY (OrderID, ProductID)
);

Defines EmployeeID as primary key and ensures Email is unique but not primary.

SQL

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Email VARCHAR(255) UNIQUE
);

Sample Program

Creates a Books table with BookID as the primary key. Inserts two books successfully. The third insert fails because BookID 1 is already used, showing how PRIMARY KEY prevents duplicates.

SQL

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(100),
  Author VARCHAR(100)
);

INSERT INTO Books (BookID, Title, Author) VALUES (1, '1984', 'George Orwell');
INSERT INTO Books (BookID, Title, Author) VALUES (2, 'To Kill a Mockingbird', 'Harper Lee');

-- This will fail because BookID 1 already exists
INSERT INTO Books (BookID, Title, Author) VALUES (1, 'Brave New World', 'Aldous Huxley');

SELECT * FROM Books;

Important Notes

PRIMARY KEY columns automatically create a unique index to speed up searches.

Trying to insert a duplicate primary key value causes an error and stops the insert.

Use PRIMARY KEY when you need a unique identifier; use UNIQUE constraint if duplicates are allowed but uniqueness is needed for some columns.

Summary

PRIMARY KEY ensures each row is unique and not null.

Only one PRIMARY KEY per table, but it can include multiple columns.

It helps keep data clean and easy to find.