CODEWITHSUNDEEP
sqlsql-serverprimary-keyidentityauto-increment
user1648502
user1648502

Reputation: 1

SQL Server 2008: how to design a table with 2 primary keys and ID with autoincrement related to 1 key value

Let me explain this a bit better: I need to design a database that needs to be installed in different locations (which might or not be connected to each other).

My thought was: why don't I use a column in which I put the information about the location (site) as a key and I have another field that contains the ID of the record? For example I need to have a Customers table that has a customer_id and a site_id column.

I know that I cannot create more than one identity columns with autoincrement in a table, though. What I ended up thinking is: is there a way to have only one identity column but related to another column?

For example it would be great if I could have a Customers table like this:

site_id cust_id description
1        1       John Doe
1        2       Joseph White
1        3       Carlos Santana
2        1       Mike Jones
2        2       Carl Johnson

The cust_id must have autoincrement.

Thank you in advance

Upvotes: 0

Views: 2016

Answers (3)

ljh
ljh

Reputation: 2594

You can create PK on two columns


create table customers (CustomerRegion int not null,
                       CustomerID int identity(1000,1) not null,
                       primary key (CustomerRegion,CustomerID)
                      )

Upvotes: 1

Dan Pichelman
Dan Pichelman

Reputation: 2332

It sounds like you want a composite primary key (see the MSDN docs here).

It's not a big deal, but it can be a royal pain in the rear later. (It's a hassle to join on multiple primary key fields)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

I have a better suggestion: If you just need the numbers on each side to be unique, why not make them so? In your design you need to repeat the value 1 in every single row in the table. This seems quite wasteful to me. Instead, try giving identity ranges:

On site 1:

CREATE TABLE dbo.Customers
(
  CustomerID BIGINT IDENTITY (1000000000,1)
  , other columns...

On site 2:

CREATE TABLE dbo.Customers
(
  CustomerID BIGINT IDENTITY (2000000000,1)
  , other columns...

Now not only are you virtually guaranteed to never create a duplicate (unless you create customers at an impressively alarming rate), but you can also immediately tell where a customer was created.

Upvotes: 1

Related Questions