SQL - PRIMARY KEY


Advertisements

Previous Page Next Page

The SQL PRIMARY KEY constraint is used to ensure that all values in a column of a table are UNIQUE and NOT NULL. A table can have only one PRIMARY KEY constraint, and a primary key can consist of single or multiple columns (fields).

SQL PRIMARY KEY constraint with CREATE TABLE statement

The below mentioned SQL code creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which PRIMARY KEY constraint is applied on column EmpID.

SQL Server / Oracle / MS Access

CREATE TABLE Employee (
  EmpID INT NOT NULL      PRIMARY KEY,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

MySQL

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

To provide a name to PRIMARY KEY constraint, and to define a PRIMARY KEY constraint on multiple columns (say EmpID and Name), the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CONSTRAINT PK_Employee PRIMARY KEY(EmpID, Name)
);

SQL PRIMARY KEY constraint with ALTER TABLE statement

In the above example, a table called Employee is created. To enforce PRIMARY KEY constraint on EmpID column, the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
ADD PRIMARY KEY (EmpID);

To provide a name to PRIMARY KEY constraint, and to define a PRIMARY KEY constraint on multiple columns (say EmpID and Name), the SQL code is given below:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmpID, Name);

DROP PRIMARY KEY constraint

To drop PK_Employee PRIMARY KEY constraint from table called Employee, the SQL code is given below:

MySQL

ALTER TABLE Employee
DROP PK_Employee;

OR 

ALTER TABLE Employee
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access

ALTER TABLE Employee
DROP CONSTRAINT PK_Employee;

Previous Page Next Page
Advertisements