Primary key Constraint

 

1) Uniquely identifies every record in table.

2) It does not allow null value.

3) It does not allow Duplicate value.

4) When we create primary key the clustered Index is generated automatically on the table.

5) 0 value is allowed in the primary key.

6) Primary key can be made foreign key on another table.

Different ways to create primary key- 

1. While creating table.

Create Table Emp

(

EmpId Int Identity(1,1) Primary Key,

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

)

2. While creating table using constraint.

Create Table Mytable2

(

    id int identity(1, 1),

    Name varchar(50),

    Constraint Const_prikey Primary Key(Id)

)

3. After table creation on New Column

Create Table Emp

(

EmpId Int,

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

)

GO

Alter Table Emp

Add Id Int Identity(1,1) primary key

4. After table creation on Existing Column, here we are adding identity it means column having value. 

Create Table Mytable3

(

    Id int identity(1, 1),

    Name varchar(50)

)

GO

Alter Table Mytable3

Add Constraint Const_primarykey Primary key (Id)

5. After table creation on Existing Column, here we will get an error because Id is nullable column. 

Create Table Emp

(

Id Int,

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

)

GO

Alter Table Emp

Add Constraint Const_PK primary Key(Id )

Error:

Msg 8111, Level 16, State 1, Line 14

Cannot define PRIMARY KEY constraint on nullable column in table 'Emp'.

Msg 1750, Level 16, State 0, Line 14

Could not create constraint or index. See previous errors.

Solution: 

Create Table Emp

(

EmpId Int Not Null,

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

)

Drop Primary Key 

1. Not reference by other table 

----------Drop Constraint

Alter Table Emp

Drop Constraint Const_PK

2. Referenced by other table  (primary key foreign key relationship)

CREATE TABLE dbo.Customer

(

Id INT Primary Key,

FirstName VARCHAR(100),

LastName VARCHAR(100),

City VARCHAR(50)

)

GO

CREATE TABLE dbo.Bill

(

BillId INT NOT Null unique,

BillNumber Varchar(20),

CustID Int Not Null Foreign key references dbo.Customer(Id)

)

When I deleting primary key of Customer Table it is not dropped because it is references by bill table. it gives an error.

If we have to drop this then first drop the constraint of foreign key of Bill table & then Drop primary key constraint of Customer table.

Alter Table Bill

Drop Constraint FK__Bill__CustID__253C7D7E

Alter Table Customer

Drop Constraint PK__Customer__3214EC07402D55DC

Composite Primary Key

If Primary key Consist of two or more columns then it is called as Composite Primary key.

CREATE TABLE dbo.Customer

(

Id INT,

FirstName VARCHAR(100),

LastName VARCHAR(100),

City VARCHAR(50)

Primary Key(Id,FirstName,LastName)

)

CREATE TABLE dbo.Customer

(

Id INT,

FirstName VARCHAR(100),

LastName VARCHAR(100),

City VARCHAR(50)

Constraint Const_Comp_Pk Primary Key(Id,FirstName,LastName)

)

GO

Insert Into Customer values (1,'sagar','pathak','Nashik')

Insert Into Customer values (2,'sagar','patil','Nashik')

if i insert same values again then will get an error,

Insert Into Customer values (1,'sagar','pathak','Nashik')---Error

---Violation of PRIMARY KEY constraint 'Const_Comp_Pk'. Cannot insert duplicate key in object 'dbo.Customer'

Insert Into Customer values (2,'sagar',null,'Nashik')---Error

---Cannot insert the value NULL into column 'LastName', table 'Database1.dbo.Customer'; 

 

CREATE TABLE dbo.Customer

(

Id INT,

FirstName VARCHAR(100),

LastName VARCHAR(100),

City VARCHAR(50)

)

GO

Alter Table Customer

Add Constraint Const_PK_Comp Primary Key(Id,FirstName,LastName)

Error:

Msg 8111, Level 16, State 1, Line 1

Cannot define PRIMARY KEY constraint on nullable column in table 'Customer'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

Solution : Apply Not Null on included Columns.

CREATE TABLE dbo.Customer

(

Id INT Not Null,

FirstName VARCHAR(100) Not Null,

LastName VARCHAR(100) Not Null,

City VARCHAR(50)

)

Drop Composite Primary Key

Alter Table Customer

Drop Constraint Const_PK_Comp

---------------

Interview Questions

Question 1: Can we Create Clustered Indexes without Primary key and non clustered index with primary key? while creating Table.

Yes, we can.

in below example i created clustered index on AadharcardNumber column & primary key on Id column.

Create Table Customer

(

ID Int Identity(1,1) Primary key ,

FirstName Varchar(100),

LastName Varchar(100),

City Varchar(100),

AadharcardNumber BIGINT Unique Clustered

)

on Id Column non clustered index will create.

Question 2: Can we Create Clustered Indexes without Primary key and non clustered index with primary key? after table creation.

Yes we can,

Create Table Customer

(

ID Int Identity(1,1) NOT NULL ,

FirstName Varchar(100),

LastName Varchar(100),

City Varchar(100),

AadharcardNumber BIGINT

)

Go

---Create Clustered  Index first 

Create Clustered  Index IX_Cust_FirstName On Customer(FirstName)

Go

---create Primary Key

Alter Table Customer

Add Constraint PK_Cust_ID Primary Key(Id)

Question 3: Can we create more than one Clustered  index on one table ? 

No

Question 4: Can clustered Index Allow Duplicate Value?

yes

Question 5: Can clustered index Allow NULL  value?

yes

Question 6: Can Primary key Allow Duplicate Value?

No

Question 7: Can Primary key Allow NULL  value?

No