StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>take a look at my answer here: <a href="https://stackoverflow.com/questions/4821831/sql-server-generate-primary-key-based-on-counter-and-another-column-value/4822494#4822494">sql server: generate primary key based on counter and another column value</a></p> <p>For your purposes, you could define your company table thus:</p> <pre><code>create table dbo.company ( id int not null primary key , name varchar(32) not null unique , order_counter not null default(0) , ... ) </code></pre> <p>and your order table thus:</p> <pre><code>create table dbo.order ( company_id int not null foreign key references dbo.company( id ) , id int not null , order_number as 100000*company_id + id , ... constraint order_AK01 unique nonclustere ( order_number ) , constraint order_PK01 primary key clustered ( company_id , id ) , ) </code></pre> <p>And set up your "add order" query thus:</p> <pre><code>declare @new_order_number int update dbo.company set @new_order_number = dbo.company.order_counter + 1 , order_counter = dbo.company.order_counter + 1 where dbo.company.id = @some_company_id insert dbo.order ( company_id , id ) value ( @some_company_id , @new_order_number ) </code></pre> <p>You have no concurrency (race) conditions: the "interlocked update" takes care of that. Further, you haven't denormalized the database design (1st normal form requires that every row and column intersection be <em>atomic</em>/non-decomposable: that it contain exactly one value from the applicable domain and nothing else. Compound identifers like yours are <em>verboten</em>.)</p> <p>Easy!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload