Can a table have multiple Primary keys?

Understanding Primary Keys: Can a Table Have Multiple Primary Keys?

Explore the concept of primary keys in relational databases, clarify common misconceptions, and understand how composite keys provide unique identification for records.

A fundamental concept in relational database design is the primary key. It serves as a unique identifier for each record within a table, ensuring data integrity and enabling efficient data retrieval. However, a common question arises: can a table have multiple primary keys? This article will clarify this concept, explain the role of primary keys, and introduce composite keys as the solution to uniquely identifying records using multiple columns.

The Role of a Primary Key

In SQL, a primary key is a column or a set of columns that uniquely identifies each row in a table. It has two main properties:

1. Uniqueness: No two rows can have the same primary key value.
2. Non-nullability: A primary key column cannot contain NULL values.

These properties are crucial for maintaining data integrity and establishing relationships between tables. A table can only have one primary key constraint defined on it. This constraint, however, can encompass one or more columns.

Introducing Composite Primary Keys

When a single column isn't sufficient to uniquely identify a record, you can use a composite primary key. This is a primary key that consists of two or more columns whose values, when combined, uniquely identify each row in the table. It's important to understand that this is still one primary key, but it's composed of multiple attributes.

For example, in a StudentCourses table, neither StudentID nor CourseID alone might be unique. A student can take multiple courses, and a course can have multiple students. However, the combination of (StudentID, CourseID) would uniquely identify a specific student's enrollment in a specific course.

erDiagram
    STUDENT ||--o{ ENROLLMENT : "enrolls in"
    COURSE ||--o{ ENROLLMENT : "has"
    
    STUDENT {
        INT StudentID PK
        VARCHAR Name
    }
    
    COURSE {
        INT CourseID PK
        VARCHAR Title
    }
    
    ENROLLMENT {
        INT StudentID PK,FK
        INT CourseID PK,FK
        DATE EnrollmentDate
    }

Entity-Relationship Diagram showing a composite primary key in the ENROLLMENT table

Implementing Composite Primary Keys in SQL

Creating a composite primary key is straightforward in SQL. You define the primary key constraint and list all the columns that form the key within the parentheses. Below are examples for MySQL and SQL Server.

MySQL

CREATE TABLE StudentCourses ( StudentID INT NOT NULL, CourseID INT NOT NULL, Grade VARCHAR(2), PRIMARY KEY (StudentID, CourseID) );

SQL Server

CREATE TABLE StudentCourses ( StudentID INT NOT NULL, CourseID INT NOT NULL, Grade NVARCHAR(2), CONSTRAINT PK_StudentCourse PRIMARY KEY (StudentID, CourseID) );

Why Not Multiple 'Primary Keys'?

The confusion often stems from the desire to uniquely identify records using different sets of columns. While you can only have one primary key constraint, you can define multiple UNIQUE constraints on a table. A UNIQUE constraint ensures that all values in a column or a set of columns are distinct, similar to a primary key, but it does allow for one NULL value (depending on the database system's implementation, e.g., SQL Server allows one NULL, MySQL treats multiple NULLs as unique).

Think of it this way: the primary key is the chosen unique identifier for the table, the one that other tables will typically reference via foreign keys. Other unique combinations are simply 'unique keys'.

CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username VARCHAR(50) NOT NULL UNIQUE,
    Email VARCHAR(100) NOT NULL UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Here, UserID is the PRIMARY KEY.
-- Username and Email each have their own UNIQUE constraints.
-- This table does not have 'multiple primary keys', but it has multiple ways to uniquely identify a user.

Example of a table with a primary key and multiple unique constraints in SQL Server.