IDENTITY key is not itself set to be Primary Key or Unique Key ??

If you have done any sort of development in database you might be familiar with IDENTITY column. It basically gives you a column which increments it self to a given value (aka Seed and Increment). General syntax for IDENTITY is,

IDENTITY [ ( seed , increment ) ]

It is basically a “Plan B” for tables where you can’t find any candidate key. By declaring a column having IDENTITY Key, you can get your self out of dilemma of what to do and how to do when your client want you to have flexibility to add any type of redundant data with still ability to distinguish between different entries !! Or simply to evade many to many (m:n) relations between tables. But as we know, it is not a good practice to simply assign an IDENTITY key and jerry rig the problem, but something we don’t have a choice (do we ever have any ?? smile_wink) …

Anyways, today’s post is about what the title says. Some people (like one of my friend) think that when we declare an Identity column, it itself becomes Unique key (because the IDENTITY key always generates UNIQUE Numbers) .. and hence it can be used as a FK even though we have not made it Primary Key or even not include UNIQUE keyword in its definition !! I have prepared a little experiment to demonstrate this …

use tempdb

-- Create sample tables 
create table table1 (id int identity(1,1))
create table table2 (id2 int identity(1,1) unique)
create table table3 (id3 int ,id4 int, foreign key (id3) references table1(id),foreign key (id4) references table2(id2))
-- HouseCleaning 
drop table table1
drop table table2
drop table table3


But when we run this query, we will get following error …

Msg 1776, Level 16, State 0, Line 1

There are no primary or candidate keys in the referenced table ‘table1’ that match the referencing column list in the foreign key ‘FK__table3__id3__45F365D3’.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

So, next time when you declare IDENTITY in Column definition, don’t forget to make it UNIQUE (which is kind of confusing because itself is UNIQUE by default) or make it PRIMARY Key to stay off to the surprise later on.

That is it for now.

It’s Just A Thought …fingerscrossed

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *