November 30, 2007 at 6:51 am
I created a table with two columns ID and Type. The ID is an Identity Seed and I created a Check Constraint on the Type column.
After testing the constraint wih a few inserts I selected the data from the table I noticed that the first ID was 3.
Each time I attempted an insert and it failed due to the Check Constraint the ID increments even though no data is inserted.
Is there a way to retain all the IDs while using a Check Constraint? or do I have to use a trigger?
November 30, 2007 at 7:05 am
AVB (11/30/2007)
I created a table with two columns ID and Type. The ID is an Identity Seed and I created a Check Constraint on the Type column.After testing the constraint wih a few inserts I selected the data from the table I noticed that the first ID was 3.
Each time I attempted an insert and it failed due to the Check Constraint the ID increments even though no data is inserted.
Is there a way to retain all the IDs while using a Check Constraint? or do I have to use a trigger?
I'm wondering whether there is a particular reason for your wish to have a contiguous sequence of numbers for your ID field. After all, rows may be deleted 🙂
Identity is good for assigning a monotonically increasing number automatically, but if you need to reuse previously used numbers you may need to look at triggers.
If you remove a very large number of identity numbers, you can reset the next identity value by setting it to the maximum value in your table (or automatically by: DBCC CHECKIDENT ( 'mytable', RESEED )
Regards,
Andras
November 30, 2007 at 8:37 am
Thanks for the reply.
No Data will be deleted from this table. Actually production data is never deleted from the DB.
I guess I'm just used to seeing IDs in succession so it looks odd to see IDs that aren't.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply