1

Context: web application, SQL Server / SQL Azure through EF Code first, asp.net mvc 3, windows azure.

I have a TPH table company which contains both customer companies and provider companies. The table contains a column type, it's the table discriminator: c for customers and p for providers.

The primary key of the table is an auto incremented integer id, managed by the database.

Now I would like to have a functional compouned key, (short_id, type), where short_id is also an autoincremented integer but I don't know how and when generate short_id values to avoid concurrency issues.

How can I prevent two different customer companies to get the same short_id if one is entered in the database in new york at the very same time the other one is entered in paris.

id name       type  short_id
1  company_a  c    1
2  company_b  c    2
3  company_c  p    1
4  company_d  c    3
5  company_e  p    2
6  company_f  c    4

I know it's unlikely since the example is dumb but the question is technical.

Thank you for reading

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mytodayquestionis
  • 47
  • 5
  • This might be helpful: http://stackoverflow.com/questions/282943/how-would-you-implement-sequences-in-microsoft-sql-server – Raihan Nov 17 '11 at 18:28
  • 1
    Almost always, you'd split out customer and provider companies to different tables anyway (both pointing to the company table). – Stu Nov 17 '11 at 19:49
  • I have to agree with @Stu, here, that it is potentially better to have a Companies table and separate Customers and Providers tables. Apart from the fact that it would help with your problem, it also helps answer the question - what if a Provider becomes a Customer in the future (or vice versa)? – Steve Morgan Nov 17 '11 at 20:58
  • the example is not relevant, in my case tph is compulsory – mytodayquestionis Nov 17 '11 at 22:12

1 Answers1

0

You might try this, though it could get slow if the table is large:

INSERT INTO company (name, type, short_id)
SELECT 'mycompany', 'c', ISNULL(MAX(short_id),0) + 1
FROM company
WHERE type = 'c'
Alex
  • 744
  • 5
  • 7