CODEWITHSUNDEEP
sqlsql-server
NocFenix
NocFenix

Reputation: 701

Can you set the max an autoincrement will reach

I want to create a table that starts at Id 300,000 and maxes out at 400,000.

Is it possible to do this in SQL Server? Or do I have to write code logic outside of that to stop any more insertions once this int of 400,000 has been reached?

Upvotes: 2

Views: 433

Answers (3)

Ben Campbell
Ben Campbell

Reputation: 398

I'd use a CHECK constraint rather than a trigger but you can easily set the start and allowed range of values for a column.

Create a test table:

IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test (
    ID INT IDENTITY(10,1) /* Start at 10, increment by 1 */
    , [Text] VARCHAR(100)
)
GO

Add constraint

ALTER TABLE #test
ADD CONSTRAINT CK_IDRange CHECK (ID >= 10 AND ID <= 20)
GO

Create some rows 

INSERT INTO #test ([Text])
VALUES (NEWID())
GO 11

SELECT * FROM #test

Fail

INSERT INTO #test ([Text])
VALUES ('should fail')

Upvotes: 3

Gimly
Gimly

Reputation: 6175

There is no option to set an upper limit to the identity column.

You can easily set the start of your identity using this:

column INT NOT NULL IDENTITY (300000, 1)

Then, for the second requirement, your should create an insert trigger that checks the number of rows (or the identity value) and return an error if your maximum has been reached. You can get an example on this SO question: Set Limit for a Table Rows In SQL

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

USE DBCC CHECKIDENT (Transact-SQL)

Checks the current identity value for the specified table in SQL Server 2016 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

And also an insert trigger to raise error when > 400k.

Upvotes: 0

Related Questions