SQL PRIMARY KEY constraints

SQL PRIMARY KEY constraints

---

The PRIMARY KEY constraint uniquely identifies each record in the database table.

The primary key must contain a unique value.

The primary key column cannot contain NULL values.

Each table should have one primary key, and each table should have only one primary key.

SQL PRIMARY KEY constraint at CREATE TABLE

---

The following SQL creates the PRIMARY KEY constraint on the "P_Id" column when the "Persons" table is created:

Mysql:

CREATE TABLE Persons                
(                
P_Id int NOT NULL,                
LastName varchar(255) NOT NULL,                
FirstName varchar(255),                
Address varchar(255),                
City varchar(255),                
PRIMARY KEY (P_Id)                
)        

SQL Server / Oracle / MS Access：

CREATE TABLE Persons                
(                
P_Id int NOT NULL PRIMARY KEY,                
LastName varchar(255) NOT NULL,                
FirstName varchar(255),                
Address varchar(255),                
City varchar(255)                
)        

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

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons                
(                
P_Id int NOT NULL,                
LastName varchar(255) NOT NULL,                
FirstName varchar(255),                
Address varchar(255),                
City varchar(255),                
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)                
)     

Note: In the example above, there is only one primary key, PRIMARY KEY (pk_PersonID). H owever, pk_PersonID value of the table is made up P_Id columns (the first and lastName).

SQL PRIMARY KEY constraint at ALTER TABLE

---

When a table has been created, to create P_Id PRIMARY KEY constraint in the "Creating" column, use the following SQL:

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons                
ADD PRIMARY KEY (P_Id)        

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

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons                
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)       

Note: If you use the ALTER TABLE statement to add a primary key, you must declare the primary key column as not containing NULL values (when the table was first created).

Undo the PRIMARY KEY constraint

---

To undo the PRIMARY KEY constraint, use the following SQL:

Mysql:

ALTER TABLE Persons                
DROP PRIMARY KEY      

SQL Server / Oracle / MS Access：

ALTER TABLE Persons               
DROP CONSTRAINT pk_PersonID