SQL-Primary Key Constraint

In this tutorial, you will learn how to use the SQL Server primary key constraint is used to combine of UNIQUE and Not Null constraints.

PRIMARY KEY Constraint

The below are the some points which defines primary key:

1. It is a combination of UNIQUE and Not Null constraints.
2. A primary key must be unique and non-null, so they're the same from that standpoint. However, a table can only have one primary key, while you can have multiple unique, non-null keys .
3. A table can have only one primary key.
4. A primary key can be defined on one column or the combination of multiple columns known as a composite primary key.
5. A primary key cannot exceed 16 columns and a total key length of 900 bytes.
6. The primary key uniquely identifies each row in a table.
7. It is often defined on the identity column. The Primary key column do not allow NULL or duplicate values. It will raise an error if try to do so.
8. All columns defined within the primary key constraint must be defined as a NOT NULL column.

The following example create a table with constraints for the columns: FirstName when the Employee table is created:

CREATE TABLE Employee (
Eid int PRIMARY KEY IDENTITY(1,1),
FirstName varchar(100) NOT NULL,
LastName varchar(100),
Emailid varchar(100),
Age int,
Departmentid int
);

The IDENTITY property is used for the Eid column to automatically generate unique integer values.

Output

Now insert some data in Employee table.

Assigning PRIMARY KEY Constraint multiple columns

To assign a particular name to a UNIQUE constraint, you use the CONSTRAINT keyword as follows:

CREATE TABLE Employee (
Eid int,
FirstName varchar(100),
LastName varchar(100) ,
Emailid varchar(100) ,
Age int,
Departmentid int,
PRIMARY KEY(Eid , Departmentid ) 
);

In this example, the values in either Eid or Departmentid column can be duplicate, but each combination of values from both columns must be unique.

Adding PRIMARY KEY Constraint to Existing Columns

Create table without primary key. The following statement creates a table without a primary key:

CREATE TABLE Employee (
Eid int,
FirstName varchar(100),
LastName varchar(100) ,
Emailid varchar(100) ,
Age int,
Departmentid int
);

To add the PRIMARY KEY constraint to an existing column, create a PRIMARY KEY constraint on the Eid column when the Employee table is already created, use the following SQL Query:

ALTER TABLE Employee 
ADD PRIMARY KEY (EID);

Drop Primary Key Constraint

Create table without primary key. The following statement creates a table without a primary key:

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100),
LastName varchar(100) ,
Emailid varchar(100) ,
Age int,
Departmentid int
);

Now adding primary key on the employee table.

Alter Table employee add constraint pk_Eid primary key(Eid)

Table with primary key looks like:

ALTER TABLE statement is used to drop a primary key in SQL Server.

ALTER TABLE [Employee] DROP CONSTRAINT pk_Eid;

Output

Now table looks like that without primary key