CODEWITHSUNDEEP
sqlsql-server
Net Citizen
Net Citizen

Reputation: 5344

MS SQL share identity seed amongst tables

In MS SQL is it possible to share an identity seed across tables? For example I may have 2 tables:

Table: PeopleA

Table: PeopleB

I'd like for PeopleA.id and PeopleB.id to always have unique values between themselves. I.e. I want them to share the same Identity seed.

Note: I do not want to hear about table partitioning please, only about if it's possible to share a seed across tables.

Upvotes: 3

Views: 3298

Answers (6)

HLGEM
HLGEM

Reputation: 96552

Original answer

No you can't and if you want to do this, your design is almost certainly flawed.

When I wrote this in 2010 that was true. However, at this point in time SQL Server now has Sequences that can do what the OP wants to do. While this may not help the OP (who surely has long since solved his problem), it may help some one else looking to do the same thing. I do still think that wanting to do this is usually a sign of a design flaw but it is possible out of the box now.

Upvotes: 4

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171391

Not sure what your design is, but sometimes it is useful to use an inheritance-type model, where you have a base table and then sub-tables with substantially different attributes, e.g.:

Person
------
PersonID <-- PK, autoincrement
FirstName
LastName
Address1
...

Employee
--------
PersonID <-- PK (not autoincrement), FK to Person
JobRoleID
StartDate
Photo
...

Associate
---------
PersonID <-- PK (not autoincrement), FK to Person
AssociateBranchID
EngagementTypeID
...

In this case you would insert the base values to Person, and then use the resulting PersonID to insert into either Employee or Associate table.

Upvotes: 3

KM.
KM.

Reputation: 103589

If you really need this, create a third table PeopleMaster, where the identity(1,1) exists, make the two other tables just have int FKs to this identity value. Insert into the PeopleMaster and then into PeopleA or PeopleB.

I would really consider this a bad design though. Create one table with a PeopleType flag ("A" or "B") and include all common columns, and create child tables if necessary (for any different columns between the PeopleA and PeopleB)

Upvotes: 2

Kane
Kane

Reputation: 16802

No.

But I have worked on projects where a similar concept was used. In my case what we did was have a table called [MasterIdentity] which had one column [Id] (an identity seed). No other table in the database had any columns with an identity seed and when Identities were required a function/stored proc was called to insert a value into the [MasterIdentity] table and return the seed.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838116

No, but I guess you could create an IDENTITY(1, 2) on the one table and an IDENTITY(2, 2) on the other. It's not a very robust design though.

Could you instead refer to your entities as 'A1', 'A2', ... if they come from TableA and 'B1', 'B2', etc... if they come from TableB? Then it's impossible to get duplicates. Obviously you don't actually need to store the A and the B in the database as it is implied.

Upvotes: 3

Cade Roux
Cade Roux

Reputation: 89661

No, there is nothing built into SQL Server to do this.

Obviously there are workarounds such as both using an FK relationship to a table which does have a single IDENTITY and having some fancy constraints or triggers.

Upvotes: 0

Related Questions