Auto increment over multiple identity columns in MS SQL 2005 or 2008

Question

Hi, I want to have two different tables in MS SQL, for example users and groups, both with identity column. It is possible to create auto increment over two identity columns? I want to have third table with common informations to these two tables and I want to identify unique record.

In Oracle is SEQUENCE.

It is possible to do it?

Thanks,

Martin Pilch

Answer 1

For SQL Server you got Identity property.

Your third table should reference users and groups via foreign key.

To achieve uniqueness of both columns in your third table, simply use unique constraint, like this:

CREATE TABLE Third (
  id_user INT references Users(id),
  id_group INT references Groups(id),
  CONSTRAINT user_group_unique UNIQUE(id_user, id_group)
);

Answer 2

I am not sure exactly what you are looking for, but you can have auto increment fields in SQL Server using the IDENTITY property:

CREATE TABLE new_employees
(
 id_num int IDENTITY(1,1),
 fname varchar (20),
 minit char(1),
 lname varchar(30)
);

In the above example (taken from the linked page), the id_num field will auto increment, starting with a seed of 1 and incrementing it by 1.

You can have such a field on each table and a many-to-many table that links the two.

Answer 3

Afaik, SQL Server does not have a concept where you can use a named identity in multiple tables (cfr. Oracles SEQUENCE)

You have a few options to differentiate the data coming from both tables

• Use a GUID as your ID column in both tables.
• In your third table, manipulate the ID of one of both tables resulting again in a unique ID (Multiply with -1 for instance). I am assuming your third table would be a view on both other tables.
• Rethink your design as to why you would need such a construct.

If the design can't be changed, using a GUID would be my choice of preference.