MySQL PRIMARY KEY

MySQL PRIMARY KEY Constraint

Every record in a table is uniquely identified by the PRIMARY KEY constraint.

Primary keys cannot have NULL values in them; instead, they must have UNIQUE values.

There can only be ONE primary key per table, and it can include one or more columns (fields) in the key.

PRIMARY KEY on CREATE TABLE

The “Persons” table is created using the following SQL, which establishes a PRIMARY KEY on the “ID” column:

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

Use the following SQL syntax to define a PRIMARY KEY constraint on several columns and to allow naming of the constraint:

				
					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: The PK_Person primary key is the only one present in the sample above. But the PRIMARY KEY VALUE consists of TWO COLUMNS (ID + LastName).

PRIMARY KEY on ALTER TABLE

When the table has already been established, use the following SQL to create a PRIMARY KEY constraint on the “ID” column:

				
					ALTER TABLE Persons
ADD PRIMARY KEY (ID);

Use the following SQL syntax to define a PRIMARY KEY constraint on several columns and to allow naming of the constraint:

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

Note: The primary key column(s) must have been declared to not contain NULL values (when the table was first formed) in order to add a primary key using ALTER TABLE.

DROP a PRIMARY KEY Constraint

Use this SQL to remove a PRIMARY KEY constraint:

				
					ALTER TABLE Persons
DROP PRIMARY KEY;

MySQL PRIMARY KEY