Unique ID, Several SQL Tables

I have 3 tables (check, active, history) which are all identical in structure (bar constraints on fields).

Data could be initally inserted onto any of the three tables, and is "unique" by way of two fields (docnum + pagenum).

I cannot use a compound primary key (as using ASPxGridView which doesn't like them), using a GUID as a primary key causes issues thanks the asp.net and is not ideal, however the docnum+pagenum is a unique key. So althought the prmary key would be some int value, the validation for uniqueness is dependant on the unique key.

The obvious solution is to use an identity column on each table.
Data will be migrated from one table to another (eg check-> active -> history) and searches are required over both the active and history tables. So there is always the possibility that when retreiving records you will get a duplicate primary key (the identity id field).

I have thought of using a master table that essentially assigns the next number in a int field on any insert to each of the three tables and then increments the number for the next insert but I fear that that will performance overhead.

What options do I have here?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

One option is to add a field to your select convert(varchar(20), docnum) + "-" + convert(varchar(20), pagenum) as ASPGrid_PK
and setting that as the PK for the gridview

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AzaniahAuthor Commented:
If doing that would you suggest then having docnum+pagenum as the primary key in the tables and not having any kind of identity field?

Determining which table the record comes from won't be an issue due to constraints on other fields.
That works for me!
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

AzaniahAuthor Commented:
Although that appears to work (and there is a method for creating a composite key in the ASPxGridView) if I did need to create a unique key across those three tables how would that be achieved?
That would be quite tricky....
First you need a table [x] that has a single identity column
Then you need a new column in the 3 tables - and you need to update all existing data with a value
Then you need to add a new int column [uid]
Finally, add a before insert trigger to each table that will perform an insert into [x] with readpast + cleanup and use the scope_identity() generated to populate the [uid] column
AzaniahAuthor Commented:
Yes thats very similar to the MasterID table I tested. I suspect that this would be a bit of a performance hit.

I'm not an expert in any of this, but my logic would assume that if I am forcing docnum+pagenum to be unique then it may as well be the primary key. They are both int fields, so the indexing will be fairly good I assume (rathar than using an alpha key - or a random GUID - both of which I suspect have potential performance hits).

Is there much difference in performance hit for a primary index being int+int as oppossed to just a single Int (as an identity field would be)?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.