SQL Server Difference between primary key and unique key

SQL Server Difference between primary key and unique key

Difference between primary key and unique key

The main objective of Primary and Unique to enforce uniqueness of column\s on which they are defined. The difference between Primary Key and Unique are listed below:

  • Primary Key doesn't allow NULL value, but in case of Unique it allows only one NULL value.
  • By default primary key creates a clustered index on the column\s, where are unique creates a non clustered index on the column\s.

Below are the SQL command for demonstrating the difference between primary and unique constraint.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE TestTable_0
(ID INT ,Name VARCHAR(10))
 
 
CREATE TABLE TestTable_1(ID INT IDENTITY(1,1),
Name VARCHAR(10))
 
CREATE TABLE TestTable_2(ID INT Primary Key,Name VARCHAR(10))
 
CREATE TABLE TestTable_3(ID INT IDENTITY(1,1) Primary Key,Name VARCHAR(10))
 
CREATE TABLE TestTable_4(ID INT IDENTITY(1,1) UNIQUE,Name VARCHAR(10))
 
CREATE TABLE TestTable_5(ID INT UNIQUE,Name VARCHAR(10))
 
CREATE TABLE TestTable_6(ID INT ,Name VARCHAR(10),
CONSTRAINT uk_ID_Name UNIQUE(ID,Name))
 
CREATE TABLE TestTable_7(ID INT ,Name VARCHAR(10),
CONSTRAINT pk_ID_Name PRIMARY KEY(ID,Name))
 
CREATE TABLE TestTable_8(ID INT ,Name VARCHAR(10),
CONSTRAINT pk_ID_Name_1 PRIMARY KEY(ID,Name),
CONSTRAINT uk_ID_Name_2 UNIQUE(ID,Name))
0 Comments
Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first

share