SQL Notes
How to use PRIMARY KEY constraints in SQL to uniquely identify each row in a table, its rules, real-world uses, and how it combines NOT NULL and UNIQUE behavior.
Every table needs a way to uniquely identify each row. Without it, how would you update one specific customer's address? How would you delete one particular order without affecting others? How would other tables reference a specific record?
The PRIMARY KEY constraint solves this fundamental problem. It uniquely identifies each row in a table by combining two important rules: the value must be unique across all rows, and it cannot be NULL. Think of it like a student's roll number — no two students share the same one, and every student must have one assigned.
Defining a Primary Key
Inline Definition
The simplest approach — add PRIMARY KEY after the column definition:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(200) NOT NULL,
Price DECIMAL(10,2)
);Table-Level Definition
Defined after all columns — useful when you want to name the constraint:
CREATE TABLE Products (
ProductID INT,
Name VARCHAR(200) NOT NULL,
Price DECIMAL(10,2),
CONSTRAINT pk_products PRIMARY KEY (ProductID)
);Auto-Increment Primary Key
In most applications, you want the database to generate primary key values automatically:
-- MySQL
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL
);
-- PostgreSQL
CREATE TABLE Customers (
CustomerID SERIAL PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL
);
-- SQL Server
CREATE TABLE Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL
);With auto-increment, you do not need to specify the primary key value during INSERT:
INSERT INTO Customers (Name, Email) VALUES ('Rahul Sharma', 'rahul@example.com');
INSERT INTO Customers (Name, Email) VALUES ('Priya Patel', 'priya@example.com');The database assigns CustomerID 1, 2, 3, and so on automatically.
Primary Key Rules
Every table has strict rules about primary keys:
| Rule | Description |
|---|---|
| Only one per table | A table can have exactly one primary key |
| No NULL values | Primary key columns cannot contain NULL |
| No duplicates | Every value must be unique |
| Cannot be changed easily | Modifying primary keys is discouraged |
| Creates a clustered index | Data is physically ordered by primary key |
Natural Key vs Surrogate Key
When choosing a primary key, you have two approaches:
Natural Key
Uses a real-world value that is already unique:
CREATE TABLE Countries (
CountryCode CHAR(2) PRIMARY KEY, -- 'IN', 'US', 'UK'
CountryName VARCHAR(100) NOT NULL
);
CREATE TABLE Books (
ISBN VARCHAR(13) PRIMARY KEY,
Title VARCHAR(300) NOT NULL,
Author VARCHAR(100)
);Surrogate Key
Uses an artificial numeric ID with no business meaning:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL UNIQUE
);Most modern applications prefer surrogate keys because they are simple, short, and never change. Natural keys can sometimes change (a country renames itself) or be too long (ISBN as a foreign key in multiple tables).
Real-World Example: E-Commerce Database
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL UNIQUE,
Phone VARCHAR(15),
Address TEXT,
JoinDate DATE DEFAULT CURRENT_DATE
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
TotalAmount DECIMAL(10,2) NOT NULL,
Status VARCHAR(20) DEFAULT 'Pending',
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);CustomerID is the primary key that other tables reference through foreign keys. Without it, there would be no reliable way to link orders to specific customers.
Real-World Example: University Database
CREATE TABLE Departments (
DeptID INT PRIMARY KEY AUTO_INCREMENT,
DeptName VARCHAR(100) NOT NULL UNIQUE,
HOD VARCHAR(100)
);
CREATE TABLE Students (
RollNo VARCHAR(15) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DeptID INT NOT NULL,
Semester INT NOT NULL DEFAULT 1,
CGPA DECIMAL(3,2) DEFAULT 0.00,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);Here RollNo is a natural primary key — it is already unique and meaningful in the university context.
Adding a Primary Key to an Existing Table
If you created a table without a primary key:
ALTER TABLE Employees
ADD PRIMARY KEY (EmpID);Or with a named constraint:
ALTER TABLE Employees
ADD CONSTRAINT pk_employees PRIMARY KEY (EmpID);This only works if the column has no NULL values and no duplicates in existing data.
Dropping a Primary Key
MySQL
ALTER TABLE Employees DROP PRIMARY KEY;PostgreSQL and SQL Server
ALTER TABLE Employees DROP CONSTRAINT pk_employees;Note: You cannot drop a primary key if other tables have foreign keys referencing it. Drop the foreign keys first.
Composite Primary Key
When no single column is unique enough, use multiple columns together:
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
EnrollmentDate DATE DEFAULT CURRENT_DATE,
Grade VARCHAR(5),
PRIMARY KEY (StudentID, CourseID)
);This is covered in detail in the Composite Keys lesson.
Common Mistakes to Avoid
Mistake 1: Using a column that might change as primary key. Email addresses, phone numbers, and names can change. Prefer stable surrogate keys.
Mistake 2: Not using AUTO_INCREMENT when appropriate. Manually managing ID values leads to conflicts in multi-user environments.
Mistake 3: Creating tables without a primary key. Every table should have a primary key. Tables without them have no guaranteed way to identify specific rows.
Mistake 4: Using overly long primary keys. Long VARCHAR primary keys slow down joins and increase index size. Prefer INT or BIGINT.
Mistake 5: Trying to add a second primary key. A table can have only one primary key. If you need additional uniqueness, use UNIQUE constraints.
Best Practices
- Every table must have a primary key — no exceptions
- Use INT AUTO_INCREMENT for most tables (simple, fast, scalable)
- Use natural keys only when they are truly stable (country codes, ISBN)
- Keep primary keys short — INT (4 bytes) is better than VARCHAR(50)
- Never update primary key values — it breaks foreign key references
- Name your constraints when using table-level syntax
Quick Summary
| Question | Answer |
|---|---|
| Can a table have multiple primary keys? | No, only one |
| Can a primary key be NULL? | No, never |
| Does it create an index? | Yes, automatically |
| Can it auto-generate values? | Yes, with AUTO_INCREMENT |
| Can other tables reference it? | Yes, via FOREIGN KEY |
The PRIMARY KEY is the most fundamental constraint in relational databases. It gives every row a guaranteed unique identity, enabling relationships between tables, fast lookups, and reliable data operations. Design your primary keys carefully — they are the foundation everything else builds upon.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for PRIMARY KEY Constraint in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, constraints, primary, key
Related SQL Complete Guide Topics