SQL PRIMARY KEY

The PRIMARY KEY constraint in SQL ensures that each record in a table is uniquely identified. It is a combination of the UNIQUE and NOT NULL constraints, meaning the column(s) defined as a primary key must contain unique values and cannot have NULL entries.

Why Use the PRIMARY KEY Constraint?

The PRIMARY KEY is essential in relational databases to:

• Ensure Unique Identification: Each row in a table can be uniquely identified.
• Prevent Missing Data: Primary key columns cannot contain NULL values.
• Support Relationships: Acts as a foundation for connecting tables through foreign keys.
• Optimize Data Retrieval: Facilitates efficient indexing for faster query performance.

Characteristics of PRIMARY KEY

• A table can have only one primary key.
• The primary key can be a single column or a combination of columns (composite key).
• Columns in a primary key are always NOT NULL by definition.

How to Use the PRIMARY KEY Constraint

The PRIMARY KEY constraint can be defined during table creation or added later using the ALTER TABLE statement.

Adding PRIMARY KEY During Table Creation

Defining a Single-Column Primary Key

1
2
3
4
5
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    EnrollmentDate DATE
);

• Ensures the StudentID column uniquely identifies each record in the Students table.

Defining a Composite Primary Key

1
2
3
4
5
6
CREATE TABLE Enrollments (
    CourseID INT,
    StudentID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (CourseID, StudentID)
);

• Combines CourseID and StudentID to ensure that each course-student pair is unique.

Add PRIMARY KEY to an Existing Table

You can use the ALTER TABLE statement to add a primary key to a table after it has been created.

Add a Single-Column Primary Key

1
2
ALTER TABLE Students  
ADD PRIMARY KEY (StudentID);

Add a Composite Primary Key

1
2
ALTER TABLE Enrollments  
ADD PRIMARY KEY (CourseID, StudentID);

Remove a PRIMARY KEY Constraint

To remove a primary key from a table, use the appropriate command based on your database system.

Drop a Primary Key

1
2
ALTER TABLE Students  
DROP PRIMARY KEY;

• This removes the primary key from the Students table.

Primary Key vs. Unique Constraint

• Purpose:
  • The PRIMARY KEY uniquely identifies each record in a table and ensures that no NULL values are present.
  • The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct but allows a single NULL value (depending on the database).
• Quantity:
  • A table can have only one primary key.
  • A table can have multiple unique constraints applied to different columns or column combinations.
• NULL Values:
  • PRIMARY KEY columns cannot contain NULL values.
  • UNIQUE columns can contain one NULL value (depending on the database system).
• Relationship Support:
  • A PRIMARY KEY is commonly used to establish relationships between tables via foreign keys.
  • A UNIQUE constraint is not typically used for defining relationships but ensures uniqueness in specific fields.
• Implicit Indexing:
  • Both PRIMARY KEY and UNIQUE constraints automatically create an index on the constrained column(s) for faster lookups.
• Use Case:
  • Use PRIMARY KEY for the main unique identifier of a table, such as OrderID or EmployeeID.
  • Use UNIQUE for secondary unique fields, like email addresses or usernames.

Best Practices for Using PRIMARY KEY

1. Use Meaningful Columns: Choose fields that uniquely identify records, such as EmployeeID, OrderID, or ISBN.
2. Avoid Large Composite Keys: Use composite keys only when necessary, and keep them minimal to simplify relationships.
3. Plan for Relationships: Define primary keys with foreign key relationships in mind to ensure smooth integration between tables.
4. Avoid Repeating Values: Ensure no duplicate or missing values in columns defined as primary keys.

Key Points to Remember

• The PRIMARY KEY constraint ensures that columns contain unique and non-null values.
• It can be defined on a single column or multiple columns (composite key).
• A table can have only one primary key, but it can span multiple columns.
• Adding or removing primary keys can be done using the ALTER TABLE statement.