Is it possible to constrain an auto-incrementing primary key with artificial limits?

Question

Can an auto-incrementing primary key be constrained by artificial limits? For example if I only want integer primary keys to be from a specific range of integers, say between 100 and 999 inclusive, and auto-increment, is that possible? And if so, on which database server software. I'm mainly interested in MS SQL Server 2000 or greater but others might be interesting to know of.

Answer 1

You can change the starting value using DBCC CHECKIDENT(<tablename>, RESEED, <newstart>); You can restrict the upper range with an ordinary CHECK constraint.

Answer 2

Yes you can do it with an identity column and a check constraint:

CREATE TABLE test(rowid int identity(100,1) primary key)
GO
ALTER TABLE test ADD CONSTRAINT CK_test_Range
    CHECK (rowid >= 100 AND rowid < 1000)
GO    
INSERT INTO test default values;
GO 900    
SELECT * FROM test
GO    
DROP TABLE test

If you don't want any gaps between the rowids, it gets a bit more complex.

Answer 3

OK you can do this as shown above, but be aware that the smaller the range, the more likely you will be to reach the point where no data can be put into the table becasue the range has been reached. And remember every rolled back transaction or deleted record takes up part of the range. I'd think very seriously before taking such a step or at least give it a range far greater than any possible number of records that you will ever have in the table.

Answer 4

Keep in mind that an IDENTITY column can have gaps in the sequence of numbers. Unused numbers are not automatically reused. So a range of 100 to 999 does not mean your table will permit exactly 900 rows - it could be something less than that.