Friday, 9 October 2015

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

No comments:

Post a Comment