Setting Primary Keys in SQL

In SQL, a Primary Key is a fundamental concept used to uniquely identify each record in a table. Every table should have a primary key to enforce data integrity and establish relationships between different tables in a relational database.

Introduction to Primary Keys

A Primary Key in SQL is a column (or set of columns) that uniquely identifies each row in a table. A primary key must satisfy the following requirements:

  • It must contain unique values for each record.
  • It cannot contain NULL values.
  • There can be only one primary key for a table.

Characteristics of a Primary Key:

  • Uniqueness: Ensures that no duplicate values exist in the column(s) used as the primary key.
  • Non-Null: A primary key field cannot be null, as every record must have a valid identifier.

Why Are Primary Keys Important?

Primary keys play a crucial role in relational databases. Here’s why they’re essential:

  • Unique Identification: Ensures that each record can be uniquely identified, which is especially important in large datasets.
  • Data Integrity: Prevents duplicate entries and enforces valid, non-null values.
  • Foreign Key Relationships: Enables relationships between tables by allowing other tables to reference primary keys.
  • Improves Query Performance: Many database systems automatically index primary keys, making lookups faster.

Defining Primary Keys on a Single Column

The most common way to set a primary key is on a single column. This is often used for tables where a single attribute can uniquely identify each record, like EmployeeID or CustomerID.

Syntax:

				
					CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    other_column data_type,
    ...
);

Example:

Let’s create a Users table where UserID serves as the primary key.

				
					CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    Email VARCHAR(100)
);

Explanation:

  • The UserID column is defined as the primary key, ensuring that each UserID is unique and non-null.

Output:

1. Valid Insertion:

				
					INSERT INTO Users (UserID, Username, Email) VALUES (1, 'Alice', 'alice@example.com');

This entry is added successfully.

2. Duplicate Primary Key Error

				
					INSERT INTO Users (UserID, Username, Email) VALUES (1, 'Bob', 'bob@example.com');
-- Error: Duplicate entry '1' for key 'PRIMARY'

This throws an error because UserID 1 already exists.

Composite Primary Keys (Primary Keys on Multiple Columns)

A Composite Primary Key is a primary key that consists of multiple columns. It’s used when a combination of columns must be unique rather than a single column alone.

Syntax:

				
					CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    PRIMARY KEY (column1, column2)
);

Example:

Suppose we have an OrderDetails table where each OrderID and ProductID pair should be unique.

				
					CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Explanation:

  • The combination of OrderID and ProductID serves as the primary key, so each product can appear only once per order.

Output:

1. Valid Insertion:

				
					INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES (101, 1, 5);

This is successful.

2. Duplicate Composite Key Error

				
					INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES (101, 1, 10);
-- Error: Duplicate entry for composite key 'OrderID-ProductID'

This insertion is blocked as the combination OrderID = 101 and ProductID = 1 already exists.

Primary Keys vs. Unique Constraints

While both unique constraints and primary keys enforce uniqueness, there are key differences:

  • Primary Key:

    • A table can have only one primary key.
    • Enforces uniqueness and does not allow NULL values.
    • Often used as a unique identifier for each row.

  • Unique Constraint:

    • A table can have multiple unique constraints.
    • Allows one NULL value (except in some database systems like Oracle).
    • Primarily used to enforce uniqueness in fields other than the primary key.

Primary Key Constraints and Auto-Increment

Auto-increment is often used with primary keys to automatically generate unique values for each record, typically in tables where an integer serves as the primary key.

Syntax:

				
					CREATE TABLE table_name (
    column_name INT PRIMARY KEY AUTO_INCREMENT,
    other_column data_type,
    ...
);

Example:

Let’s create a Products table where ProductID auto-increments with each new product.

				
					CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2)
);

Explanation:

  • Each time a new product is added, ProductID automatically increments by 1.

Output:

1. Insertions with Auto-Increment

				
					INSERT INTO Products (ProductName, Price) VALUES ('Laptop', 899.99);
INSERT INTO Products (ProductName, Price) VALUES ('Smartphone', 499.99);

Result:

  • First insertion assigns ProductID = 1 to ‘Laptop.’
  • Second insertion assigns ProductID = 2 to ‘Smartphone.’

Modifying Primary Keys in Existing Tables

To add a primary key to an existing table, use the ALTER TABLE command.

Syntax:

				
					ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Example:

Assume we want to add a primary key on CustomerID in the Customers table.

				
					ALTER TABLE Customers
ADD PRIMARY KEY (CustomerID);

To remove a primary key:

				
					ALTER TABLE table_name
DROP PRIMARY KEY;

Best Practices for Setting Primary Keys

Here are some best practices to keep in mind when setting primary keys:

  1. Use Integer Types for Auto-Increment IDs: Integer primary keys perform well and are compatible with auto-incrementing.
  2. Choose Fields that Do Not Change: Avoid fields that might change over time as primary keys (e.g., emails, phone numbers).
  3. Avoid Using Composite Keys: Where possible, use single-column primary keys for simplicity and efficiency.
  4. Use Surrogate Keys: Sometimes, it’s best to create a unique, meaningless ID (e.g., UserID), known as a surrogate key, rather than using business-specific data.

Example of Using Primary Keys in Relational Tables

Let’s create two related tables using primary keys and foreign keys.

Example:

				
					CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100)
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(100),
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

Explanation:

  • Authors table has AuthorID as the primary key.
  • Books table has BookID as the primary key and a foreign key reference to AuthorID in Authors.

Output:

1. Insertions and References:

				
					INSERT INTO Authors (Name) VALUES ('J.K. Rowling');
INSERT INTO Books (Title, AuthorID) VALUES ('Harry Potter and the Sorcerer''s Stone', 1);

This creates a relationship where the book is linked to its author through the AuthorID.

Primary keys are essential for building structured, relational databases. They provide unique identification for each record, enforce data integrity, and enable relationships across tables. By following best practices and understanding different approaches to primary keys, you can create robust database structures that ensure data accuracy and consistency. Happy Coding!❤️

Table of Contents

Home
Roadmap
ATS Resume
Playground
Resources