PRIMARY KEY in SQL

🔑 The PRIMARY KEY constraint in SQL is used to uniquely identify each row in a table. A primary key ensures that every record has a unique identifier, making it one of the most important constraints in relational databases.

📖 What is a PRIMARY KEY?

A PRIMARY KEY is a column or a combination of columns whose values uniquely identify every row in a table. A primary key cannot contain duplicate values and cannot contain NULL values.

Information

Every table should ideally have a primary key to uniquely identify each record and support relationships with other tables.

đŸŽ¯ Why Use PRIMARY KEY?

Primary keys provide a reliable way to identify records and maintain relationships between tables.

  • 📌 Ensure every row is uniquely identifiable.
  • 📌 Prevent duplicate records.
  • 📌 Disallow NULL values.
  • 📌 Support relationships using foreign keys.
  • 📌 Improve query performance through indexing.

📝 Basic Syntax

PRIMARY KEY Syntax

CREATE TABLE table_name
(
    column_name data_type PRIMARY KEY
);

💡 Create a Table with a PRIMARY KEY

Create a Students table where StudentID uniquely identifies every student.

Single-Column PRIMARY KEY

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

In this table, every student must have a unique StudentID.

✅ Valid INSERT

This statement succeeds because the primary key value is unique.

Valid INSERT

INSERT INTO Students
(StudentID, Name, Department, Age)
VALUES
(101, 'Alice', 'Computer Science', 20);

❌ Duplicate PRIMARY KEY

Attempting to insert another row with the same primary key causes an error.

Duplicate PRIMARY KEY

INSERT INTO Students
(StudentID, Name, Department, Age)
VALUES
(101, 'Bob', 'Mathematics', 21);

Error

The database rejects the insert because the StudentID value already exists.

❌ NULL PRIMARY KEY

A primary key cannot contain NULL.

NULL PRIMARY KEY

INSERT INTO Students
(StudentID, Name)
VALUES
(NULL, 'Charlie');

Error

The insert fails because primary key columns must always contain a valid, non- NULL value.

🔗 Composite PRIMARY KEY

A primary key can consist of multiple columns. This is known as a composite primary key.

Composite PRIMARY KEY

CREATE TABLE Enrollments
(
    StudentID INT,
    CourseID INT,
    Semester VARCHAR(20),

    PRIMARY KEY (StudentID, CourseID)
);

In this example, the combination of StudentID and CourseID must be unique.

➕ Add a PRIMARY KEY to an Existing Table

A primary key can also be added after a table has been created.

Add PRIMARY KEY

ALTER TABLE Students
ADD CONSTRAINT PK_Students
PRIMARY KEY (StudentID);

Warning

Existing rows must contain unique, non- NULL values before a primary key can be added.

➖ Drop a PRIMARY KEY

The syntax for removing a primary key depends on the database system.

Drop PRIMARY KEY (MySQL Example)

ALTER TABLE Students
DROP PRIMARY KEY;

Drop PRIMARY KEY (SQL Server Example)

ALTER TABLE Students
DROP CONSTRAINT PK_Students;

📊 Example Table

StudentIDNameDepartment
101AliceComputer Science
102BobMathematics
103CharliePhysics

⚖ī¸ PRIMARY KEY vs UNIQUE

FeaturePRIMARY KEYUNIQUE
Duplicate Values Allowed❌ No❌ No
NULL Allowed❌ NoDepends on the database system.
Number Allowed Per TableOneMultiple
Main PurposeIdentify each row.Prevent duplicate values.

đŸ’ŧ Real-World Example

In an online banking system, every customer receives a unique customer ID. This identifier is used throughout the database to link accounts, transactions, loans, and other records.

Customers Table

CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    FullName VARCHAR(100),
    Email VARCHAR(150),
    Phone VARCHAR(20)
);

⚠ī¸ Common Mistakes

  • ❌ Choosing a column whose values may change frequently.
  • ❌ Attempting to insert duplicate primary key values.
  • ❌ Allowing business rules to rely on non-unique columns.
  • ❌ Trying to create a primary key on columns containing duplicate or NULL values.

Warning

Primary key values should remain stable whenever possible. Frequently changing primary keys can complicate relationships with other tables.

⚠ī¸ Best Practices

Best Practice

Define a primary key for every table, choose short and stable values, use surrogate keys such as auto-increment integers when appropriate, avoid using frequently changing columns as primary keys, and use composite primary keys only when they naturally represent a unique relationship.

🚀 Key Points to Remember

  • 📌 A PRIMARY KEY uniquely identifies each row.
  • 📌 Duplicate values are not allowed.
  • 📌 NULL values are not allowed.
  • 📌 Each table can have only one primary key, but it may contain multiple columns.
  • 📌 Primary keys are commonly referenced by foreign keys.
  • 📌 Choosing the right primary key improves database integrity and performance.
>>"Every reliable relational database begins with a strong primary key that uniquely identifies every record."

Summary

✅ The PRIMARY KEY constraint is the foundation of relational database design. It uniquely identifies every row, prevents duplicate and NULL values, and enables relationships between tables through foreign keys. A well-designed primary key improves data integrity, performance, and maintainability.