Is UNIQUE and NOT NULL makes PRIMARY KEY

Introduction

In this article we are trying to establish a case of UNIQUE constraint with NOT NULL clause and try to understand it as a logical Primary key. Hope it will be informative.

So What is the Technical Definition of a Primary Key

Primary Key  = UNIQUE  +  NOT NULL + One Index (Default is Clustered Index).

If a table has no clustered index when we create the Primary key the Clustered Index is created automatically. But if the table has the Clustered Index in the other columns when we create the Primary key the NON CLUSTERED index is created in that column.

Try to take an Example with Clustered/Non Clustered Primary Key

IF OBJECT_ID(N'dbo.tbl_Example', N'U') IS NOT NULL

   DROP TABLE dbo.tbl_Example;

GO

CREATE TABLE dbo.tbl_Example

   (

     idProduct          INT          NOT NULL,

       ProductName      VARCHAR(50)  NOT NULL

   );

GO

-- Now we create a Clustered Index on tbl_Example.ProductName Columns

CREATE CLUSTERED INDEX IX_CLUS_ProductName ON dbo.tbl_Example(ProductName);

GO

-- Now we Create a PK on tbl_Example.idProduct

ALTER TABLE dbo.tbl_Example

ADD CONSTRAINT PK_idProduct PRIMARY KEY(idProduct);

GO

sp_helpindex tbl_Example

index_name	index_description	index_keys
IX_CLUS_ProductName	clustered located on PRIMARY	ProductName
PK_idProduct	nonclustered, unique, primary key located on PRIMARY	idProduct

If we look at the example we find that in the column ProductName we already create the Clustered index before and after that we make the primary key on    idProduct.

If we make the closer look, we find that the Primary key can Exists with Non Clustered Index.

Now Take another Example

Unique Key / Primary Key

IF OBJECT_ID(N'dbo.tbl_Example', N'U') IS NOT NULL

   DROP TABLE dbo.tbl_Example;

GO

CREATE TABLE dbo.tbl_Example

   (

     idProduct    INT          NOT NULL PRIMARY KEY,

     ProductName  VARCHAR(50)  NOT NULL UNIQUE

   );

GO

GO

sp_helpindex tbl_Example

index_name	index_description	index_keys
PK__tbl_Exam__5EEC79D1CCF2E1D6	clustered, unique, primary key located on PRIMARY	idProduct
UQ__tbl_Exam__DD5A978AC582F645	nonclustered, unique, unique key located on PRIMARY	ProductName

If we look at the example we find that a non-Clustered Index is created in the columns ProductName where we make the UNIQUE constraint.

If we look at the table definition we find that ProductName column have Unique and NOT NULL constraint.

So in this case

ProductName = UNIQUE + NOT NULL + Non Clustered Index

If the Primary key is removed form that table can we use the ProductName columns as primary key for that Table?

What is your opinion related to it.

Posted By: JOYDEEP DAS