SQL PRIMARY KEY

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint ensures each record in a table is uniquely identified and must contain unique values, disallowing NULL values.

A table can have only one primary key, which can be composed of a single or multiple columns (fields).

SQL PRIMARY KEY on CREATE TABLE

The given SQL statement establishes a PRIMARY KEY on the “ID” column during the creation of the “Persons” table.

MySQL:

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

SQL Server / Oracle / MS Access:

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

To enable naming of a PRIMARY KEY constraint and to define it across multiple columns, utilize the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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 provided example, there is just a single primary key (PK_Person). However, this primary key consists of the combined values of two columns (ID + LastName).

SQL PRIMARY KEY on ALTER TABLE

To establish a PRIMARY KEY constraint on the “ID” column after the table has been created, employ the following SQL statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

For enabling the naming of a PRIMARY KEY constraint and defining it across multiple columns, utilize the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

Note: When utilizing ALTER TABLE to add a primary key, it’s essential that the primary key column(s) were initially declared to disallow NULL values when the table was created.

DROP a PRIMARY KEY Constraint

To remove a PRIMARY KEY constraint, employ the following SQL statement:

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;