The PRIMARY KEY constraint is used to uniquely identify each row in a table. A PRIMARY KEY must contain unique values and it can’t contain a null value. A table can have only one primary key. We can use multiple columns or fields to define a primary key, such primary key is known as composite key.
Syntax of PRIMARY KEY Constraint on one column with CREATE TABLE statement:
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
PRIMARY KEY (P_Id))
CREATE TABLE Persons
(
P_Id int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
PRIMARY KEY (P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
)
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
)
Syntax of PRIMARY KEY Constraint on one column with ALTER TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE CUSTOMER ADD PRIMARY KEY(ID);
ALTER TABLE CUSTOMER ADD PRIMARY KEY(ID);
Syntax of PRIMARY KEY Constraint on multiple columns with CREATE TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
PRIMARY KEY (P_Id, FirstName))
CREATE TABLE Persons
(
P_Id int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25),
Address varchar(255),
PRIMARY KEY (P_Id, FirstName)
)
Syntax of PRIMARY KEY Constraint on multiple columns with ALTER TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT PK_PersonID
PRIMARY KEY(P_Id, FirstName);
ALTER TABLE Persons ADD CONSTRAINT PK_PersonID
PRIMARY KEY(P_Id, FirstName);