MySQL PRIMARY KEY Constraint

  1. Overview
  2. MYSQL
  3. MySQL Database
  4. MySQL PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify each record in a database table. This constraint ensures that the values in the specified column(s) are unique and not NULL. Each table can only have one PRIMARY KEY, and the PRIMARY KEY can consist of one or multiple columns (fields).

Key Characteristics of PRIMARY KEY:

  • Uniqueness: A PRIMARY KEY column must have unique values for each row in the table.
  • Non-NULL: A PRIMARY KEY column cannot contain NULL values. Every row must have a value for the PRIMARY KEY column.
  • Single Primary Key: A table can only have one PRIMARY KEY constraint, but this constraint can be made up of multiple columns (composite primary key).

Using the PRIMARY KEY Constraint

1. PRIMARY KEY on Table Creation

You can apply a PRIMARY KEY constraint when creating a table. This ensures that the values in the specified column(s) are both unique and non-NULL.

Example: Create Table with PRIMARY KEY on a Single Column
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    PRIMARY KEY (ID)
);

In this example:

  • The ID column is the PRIMARY KEY, ensuring that every row has a unique and non-NULL value in the ID column.
Example: Create Table with PRIMARY KEY on Multiple Columns (Composite Primary Key)
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)
);

In this example:

  • The PRIMARY KEY consists of both the ID and LastName columns, making the combination of ID and LastName unique across the table.
  • The constraint is named PK_Person.

2. Adding a PRIMARY KEY to an Existing Table

You can add a PRIMARY KEY constraint to an existing table using the ALTER TABLE statement.

Example: Add PRIMARY KEY on an Existing Column
ALTER TABLE Persons
ADD PRIMARY KEY (ID);

In this example:

  • The ID column is now set as the PRIMARY KEY of the Persons table.
Example: Add PRIMARY KEY on Multiple Columns (Composite Primary Key)
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

In this example:

  • A composite PRIMARY KEY is added, using both ID and LastName columns to uniquely identify each record.
  • The constraint is named PK_Person.

3. Dropping a PRIMARY KEY Constraint

If you no longer need the PRIMARY KEY on a table, you can remove it using the ALTER TABLE statement.

Example: Drop a PRIMARY KEY Constraint
ALTER TABLE Persons
DROP PRIMARY KEY;

In this example:

  • The PRIMARY KEY constraint is dropped from the Persons table.

Important Notes:

  • One Primary Key: A table can only have one PRIMARY KEY constraint, but the key can consist of multiple columns, making it a composite primary key.
  • NULL Values: You cannot have NULL values in any column that is part of the PRIMARY KEY. If any row has a NULL in the PRIMARY KEY column(s), it will violate the constraint.
  • Composite Primary Key: A composite primary key uses multiple columns to ensure uniqueness. The combination of these columns must be unique across all records in the table.

Summary of PRIMARY KEY Constraint Usage:

  • On Table Creation: The PRIMARY KEY constraint is defined during table creation, either on a single column or multiple columns (composite primary key).
  • On Table Modification: You can add a PRIMARY KEY constraint to an existing table using the ALTER TABLE statement.
  • Dropping the PRIMARY KEY: The PRIMARY KEY can be removed using the ALTER TABLE statement with DROP PRIMARY KEY.

The PRIMARY KEY constraint is crucial for ensuring that each record in a table can be uniquely identified and accessed, which is fundamental for relational database design.

Was this article helpful?