SQL PRIMARY KEY Constraint

Every table must be identified with a unique key or the Primary Key. Primary Key uniquely identifies each record in the table. It makes searching of record faster.
A Primary Key cannot contain a NULL values and must be unique in every record.

Different DBMS have different implementations to set the primary keys, so let’s see each of them below.

SQL PRIMARY KEY Syntax

CREATE TABLE TABLE_NAME
  (
  column_name1 data_type CONSTRAINT PRIMARY KEY,
  column_name2 data_type   NULL/NOT NULL  ,
  column_name3 data_type   NULL/NOT NULL,....)

Example For Oracle/SQL Server

CREATE TABLE Users
  (
  ID INT NOT NULL PRIMARY KEY,
  Firstname   VARCHAR(255)   NOT NULL,
  Lastname   VARCHAR(255),
  Salary FLOAT NOT NULL,
  DeptID INT NOT NULL)

Users

Example For MySQL

CREATE TABLE Users
  (
  ID   INT NOT NULL,
  Firstname   VARCHAR(30)   NOT NULL,
  Lastname   VARCHAR(30),
  Salary FLOAT NOT NULL,
  DeptID INT NOT NULL,
  PRIMARY KEY (ID)
  )

Users

We have set the primary key for the column ID in the users table

ALTER TABLE method

Example

ALTER TABLE Users
     ADD PRIMARY KEY (column_name)

Users

TO REMOVE PRIMARY KEY

Example

ALTER TABLE Users
DROP PRIMARY KEY