How many primary keys are in a table?

In SQL, a table can have only one primary key. However, this primary key can be composed of a single column or multiple columns, known as a composite primary key. Understanding the nature and limitations of primary keys is essential for effective database design, ensuring data integrity, and establishing clear relationships between tables.

1. Single-Column Primary Key

A single-column primary key uses one column to uniquely identify each record in a table. This is the most straightforward and commonly used form of primary key.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

Explanation:

• EmployeeID is the primary key.
• Each EmployeeID value must be unique and cannot be NULL, ensuring that each employee is uniquely identifiable.

2. Composite Primary Key

A composite primary key uses two or more columns together to uniquely identify each record. This is useful when no single column can guarantee uniqueness on its own, but a combination of columns can.

Example:

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.
• Neither OrderID nor ProductID alone can uniquely identify a record, but together they ensure that each product within an order is distinct.

3. Characteristics of Primary Keys

Regardless of whether a primary key is single-column or composite, it must adhere to the following characteristics:

1. Uniqueness:

   • Every value of the primary key must be unique across the table.
   • No two rows can have the same primary key value.

2. Non-Nullability:

   • Primary key columns cannot contain NULL values.
   • Each record must have a valid primary key value to ensure its existence and identity.

3. Immutability:

   • Ideally, primary key values should not change over time.
   • Changing primary key values can disrupt relationships and referential integrity within the database.

4. Single Primary Key Constraint:

   • A table can have only one primary key constraint.
   • However, this primary key can encompass multiple columns if needed.

4. Primary Key vs. Unique Key

It's important to distinguish between primary keys and unique keys, as they both enforce uniqueness but serve different purposes.

Example of Unique Key:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Email VARCHAR(100) UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL
);

• UserID is the primary key.
• Username and Email are unique keys, ensuring that each username and email address is unique across the table.

5. Practical Implications

Having only one primary key per table ensures a clear and unambiguous method for identifying records. This uniqueness is foundational for:

• Referential Integrity:
  • Other tables can reference the primary key via foreign keys, establishing clear and reliable relationships.
• Efficient Querying:
  • Primary keys are often indexed automatically, enhancing the performance of queries that search based on these keys.
• Data Integrity:
  • Ensuring that each record is uniquely identifiable prevents data anomalies and maintains the accuracy of the database.

6. Example Across Different SQL Dialects

While the concept of a single primary key per table is consistent across SQL dialects, the syntax for defining composite primary keys may vary slightly.

MySQL Example:

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

PostgreSQL Example:

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

SQL Server Example:

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

In all cases, the combination of StudentID and CourseID uniquely identifies each enrollment record, preventing duplicate enrollments for the same student in the same course.

7. Best Practices for Primary Keys

1. Use Surrogate Keys:
   • Instead of relying on natural data (like Social Security Numbers), use surrogate keys (e.g., auto-incrementing integers) to ensure uniqueness without depending on business data.
2. Keep It Simple:
   • Prefer single-column primary keys when possible to simplify table relationships and indexing.
3. Ensure Immutability:
   • Choose primary key columns that are unlikely to change, maintaining referential integrity over time.
4. Consistent Data Types:
   • Use consistent and appropriate data types for primary key columns to optimize performance and storage.
5. Document Primary Keys:
   • Clearly document the purpose and structure of primary keys within your database schema for better maintainability and understanding.

8. Handling Primary Keys in Operations

When performing operations like inserting, updating, or deleting records, the uniqueness of the primary key must be maintained.

Inserting Records:

-- Correct insertion
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

-- Attempting to insert a duplicate primary key will result in an error
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (1, 'Jane', 'Smith', 'jane.smith@example.com');

Error Message:

ERROR: duplicate key value violates unique constraint "Employees_pkey"
DETAIL: Key (EmployeeID)=(1) already exists.

Deleting Records:

DELETE FROM Employees
WHERE EmployeeID = 1;

Updating Records:

UPDATE Employees
SET Email = 'john.newemail@example.com'
WHERE EmployeeID = 1;

Note: You cannot update a primary key value to one that already exists in the table, as this would violate the uniqueness constraint.

Conclusion

In SQL, a table is restricted to having only one primary key, whether it is a single column or a combination of multiple columns. This primary key is crucial for uniquely identifying each record, maintaining data integrity, establishing relationships between tables, and optimizing query performance. Adhering to best practices in defining and managing primary keys ensures a robust and efficient database structure, facilitating reliable data operations and interactions.

Key Takeaways:

• Single Primary Key per Table: Each table can have only one primary key, which can be single-column or composite.
• Uniqueness and Non-Nullability: Primary keys must contain unique and non-null values.
• Facilitates Relationships: Primary keys are essential for establishing foreign key relationships with other tables.
• Best Practices: Use surrogate keys, keep keys simple and immutable, and maintain consistent data types.

By understanding and implementing these principles, you can design and maintain effective and reliable relational databases.