🔑 SQL PRIMARY KEY Constraint – Ensure Uniqueness and Integrity in Your Tables

In SQL, a PRIMARY KEY is a constraint used to uniquely identify each record in a table. It ensures no duplicates and no NULL values for the specified column(s).

Setting a primary key is a fundamental best practice in relational database design and helps enforce data accuracy and consistency.

📘 What is a PRIMARY KEY in SQL?

A PRIMARY KEY is a constraint that uniquely identifies each row in a table. Each table can have only one primary key, but it can be made of one or multiple columns (called a composite key).

✅ A column defined as a PRIMARY KEY must be:

Unique (no two rows can have the same value)
Not NULL

🧾 SQL PRIMARY KEY Syntax

Define in column-level (single column key):

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50)
);

Define in table-level (multi-column/composite key):

CREATE TABLE orders (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

✅ Example: Create a Table with a PRIMARY KEY

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100)
);

In this case:

customer_id becomes the unique identifier
No two customers can have the same customer_id
You must provide a value (can’t be NULL)

➕ Adding PRIMARY KEY to an Existing Table

If you forget to add a primary key during table creation, you can do it later:

ALTER TABLE customers
ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id);

❌ What Happens If You Try to Insert Duplicates?

INSERT INTO customers (customer_id, first_name)
VALUES (1, 'John');

-- Try again
INSERT INTO customers (customer_id, first_name)
VALUES (1, 'Mike');

🔁 You’ll get an error like:
“Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object…”

⚠️ Rules of PRIMARY KEY in SQL

Rule	Explanation
Only one PRIMARY KEY per table	You can’t define multiple primary keys
Cannot contain NULL	All key columns must have values
Must be unique	Each row must be distinguishable
Can be composite (multiple columns)	You can use 2+ columns together as a key
Automatically indexed	Improves lookup performance

🧠 Why Use a PRIMARY KEY?

Guarantees data uniqueness
Prevents duplicate or null entries
Establishes relationships via foreign keys
Enhances query speed through automatic indexing
Critical for normalization and referential integrity

📝 Summary

A PRIMARY KEY is used to uniquely identify records
It must be unique and not null
Only one primary key is allowed per table
Can be created on one or multiple columns
It’s automatically indexed for fast retrieval

Primary Key