CODEWITHSUNDEEP
sqlidentity
dg90
dg90

Reputation: 1263

Possible to manage identity range in SQL server?

Is it possible to manage an identity range in SQL Server?

If I set seed in DB one on 1 and in DB two on 10000

In this scenario if I sync both DB's, DB one just adds on 10000

Let's say I want this:

SOLUTION:

ALTER TABLE dbo.* WITH NOCHECK 
    ADD CONSTRAINT Id_Range_Constraint
    CHECK(ID BETWEEN 10000 AND 19999)

Upvotes: 1

Views: 2021

Answers (2)

Diego
Diego

Reputation: 36156

you will need a check on the DB1 as @Yuck said and on DB2, create the identity as:

id int not null primary key identity (10000,1),

Upvotes: 0

Yuck
Yuck

Reputation: 50855

You could create constraints in both databases for the ranges specified in your question. But keep in mind that INSERT will fail as soon as records fall outside that range.

Something along these lines, for SQL Server. Other RDBMS syntax will vary slightly:

CREATE TABLE T (
  Id Int PRIMARY KEY IDENTITY(0, 1)
);

ALTER TABLE T ADD CONSTRAINT Id_Range_Constraint
CHECK (Id BETWEEN 0 AND 9000);

DROP TABLE T;

Upvotes: 2

Related Questions