Reseeding to a value that exists

Hi group,
If I have a table, SomeTable like so:
(SQL Server 2000)
[ID] [int] IDENTITY (1, 1) NOT NULL
[SomeValue] [varchar] (50)
ID SomeValue
1 something
2 something other
3 something else
And I then execute:
DBCC CHECKIDENT (SomeTable, RESEED, 2)
And I then execute:
INSERT INTO SomeTable VALUES('something more')
This will leave me with two values with the same ID of 3. What I expected
was that I'd receive an error when trying to reseed at 2, when 2 already
exists, but this is not the case. Is this a DB option that I do not have
set that allows duplicate identities, and/or is there something that I can
change so that if I am ever stupid enough to reseed at an existing value,
that I will be denied with an error?
Thank you,
Ray at workAny of the following will guarantee uniqueness:
1) primary key constraint
2) unique constraint
3) unique index
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:%23Z7Db0gqDHA.1444@.tk2msftngp13.phx.gbl...
> Hi group,
> If I have a table, SomeTable like so:
> (SQL Server 2000)
> [ID] [int] IDENTITY (1, 1) NOT NULL
> [SomeValue] [varchar] (50)
> ID SomeValue
> 1 something
> 2 something other
> 3 something else
> And I then execute:
> DBCC CHECKIDENT (SomeTable, RESEED, 2)
> And I then execute:
> INSERT INTO SomeTable VALUES('something more')
> This will leave me with two values with the same ID of 3. What I
expected
> was that I'd receive an error when trying to reseed at 2, when 2
already
> exists, but this is not the case. Is this a DB option that I do not
have
> set that allows duplicate identities, and/or is there something that I
can
> change so that if I am ever stupid enough to reseed at an existing
value,
> that I will be denied with an error?
> Thank you,
> Ray at work
>
>