Primary key constraint in SQL

A primary key constraint depicts a key comprising one or more columns that will help uniquely identify every tuple/record in a table.

Properties :

1. No duplicate values are allowed, i.e. Column assigned as primary key should have UNIQUE values only.
2. NO NULL values are present in column with Primary key. Hence there is Mandatory value in column having Primary key.
3. Only one primary key per table exist although Primary key may have multiple columns.
4. No new row can be inserted with the already existing primary key.
5. Classified as : a) Simple primary key that has a Single column 2) Composite primary key has Multiple column.
6. Defined in Create table / Alter table statement.

The primary key can be created in a table using PRIMARY KEY constraint. It can be created at two levels.

1. Column
2. Table.

SQL PRIMARY KEY at Column Level :
If Primary key contains just one column, it should be defined at column level. The following code creates the Primary key “ID” on the person table.

Syntax :

Create Table Person
(
Id int NOT NULL PRIMARY KEY, 
Name varchar2(20), 
Address varchar2(50)
);

Here NOT NULL is added to make sure ID should have unique values. SQL will automatically set null values to the primary key if it is not specified.

Example-1 :
To verify the working of Primary key :

Insert into Person values(1, 'Ajay', 'Mumbai');

Output :

1 row created

Example-2 :
Let’s see if you will insert the same values again.

Insert into Person values(1, 'Ajay', 'Mumbai');

Output :

Error at line 1: unique constraint violated

Since we are inserting duplicate values, an error will be thrown since the Primary key “Id” can contain only unique values.

Example-3 :

Insert into Person values('', 'Ajay', 'Mumbai');

Output :

Error at line 1: cannot insert Null into<"user"."Person"."ID">

Primary Key cannot contain Null Values so That too will throw an error.

SQL PRIMARY KEY at Table Level :
Whenever the primary key contains multiple columns it has to be specified at Table level.

Syntax:

Create Table Person
(Id int NOT NULL, 
Name varchar2(20), 
Address varchar2(50), 
PRIMARY KEY(Id, Name)
);                

Here, you have only one Primary Key in a table but it consists of Multiple Columns(Id, Name). However, the Following are permissible.

Insert into Person values(1, 'Ajay', 'Mumbai');
Insert into Person values(2, 'Ajay', 'Mumbai');

Since multiple columns make up Primary Key so both the rows are considered different. SQL permits either of the two values can be duplicated but the combination must be unique.

SQL PRIMARY KEY with ALTER TABLE :
Most of the time, Primary Key is defined during the creation of the table but sometimes the Primary key may not be created in the already existing table. We can however add Primary Key using Alter Statement.

Syntax :

Alter Table Person add Primary Key(Id);

To add Primary key in multiple columns using the following query.

Alter Table Person add Primary Key(Id, Name);

It is necessary that the column added as primary key MUST contain unique values or else it will be violated. An id cannot be made Primary key if it contains duplicate values. It violates the basic rule of Primary Key. Altering the table to add Id as a primary key that may contain duplicate values generates an error.

Output :

Error at line 1: cannot validate- primary key violated

Also, A column added as primary key using alter statement should not have null values. Altering table to add Id as primary key that may contain null values generates an error.

Output :

Error at line 1: column contains NULL values; cannot alter to NOT NULL

DELETE PRIMARY KEY CONSTRAINT :
To remove Primary Key constraint on table use given SQL as follows.

ALTER table Person DROP PRIMARY KEY;