MySQL PRIMARY KEY

MySQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must be unique and cannot contain NULL values.

Each table can have only one primary key, which can consist of one or multiple columns (fields).

PRIMARY KEY on CREATE TABLE

The following SQL statement establishes a PRIMARY KEY constraint on the “ID” column during the creation of the “Persons” table:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

To name a PRIMARY KEY constraint and define it for multiple columns, use the following SQL syntax:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Note: In the example above, there is only one PRIMARY KEY (PK_Person). However, the primary key value is composed of two columns (ID + LastName).

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

To assign a name to a PRIMARY KEY constraint and define it for multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Note: When using ALTER TABLE to add a primary key, the column(s) designated as the primary key must have been initially declared as NOT NULL when the table was created.

DROP a PRIMARY KEY Constraint

To remove a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons
DROP PRIMARY KEY;