Must an identity column be also unique?

24 Aug 2006 10:26 AM

Bob

you would think it should be...but you can reset it.....

what if you ran up number 1 to 10,000...then said i tyhink i will set
it back to 1......

can you do that....not that you would want to....but....

24 Aug 2006 11:02 AM

David Portas

Bob wrote:

> you would think it should be...but you can reset it.....
>
> what if you ran up number 1 to 10,000...then said i tyhink i will set
> it back to 1......
>
> can you do that....not that you would want to....but....

It doesn't have to be. You can populate it with any values you like
using the SET IDENTITY_INSERT option. You have to add a UNIQUE or
PRIMARY KEY constraint to guarantee its uniqueness.

I can't think of a good reason why you wouldn't add a constraint to an
IDENTITY column but it has been known for developers to neglect putting
constraints in :-)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

24 Aug 2006 12:06 PM

Hari Prasad

Hi,

You can Reseed the Identity value to 1. Ensure that there is not Primary Key
and Unique Constraints associated with that table.
If you have any one of those constrain violation will come when the next
insert happens.

Thanks
Hari
SQL Server MVP

Show quote

"Bob" <Go1***@Yahoo.Com> wrote in message
news:1156415200.825141.73270@i3g2000cwc.googlegroups.com...
> you would think it should be...but you can reset it.....
>
> what if you ran up number 1 to 10,000...then said i tyhink i will set
> it back to 1......
>
> can you do that....not that you would want to....but....
>