Skip to main content

SQL PRIMARY KEY Constraint

Body

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

ID Firstname Lastname Salary DeptID

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

ID Firstname Lastname Salary DeptID

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

ID Firstname Lastname Salary deptnumber

TO REMOVE PRIMARY KEY

Example

ALTER TABLE Users DROP PRIMARY KEY

Add new comment