Unique Key in Multiple Tables

I'm setting up a database with multiple tables and need to have a unique primary key between two of the tables. For example:
Table 1:
1 Row A
2 Row B
3 Row C

Then, when I add a row to Table B, it sets its key to 4. How can I set this up?

LVL 15
Who is Participating?
danthsConnect With a Mentor Commented:
what szacks suggested is technically feasible however a cleaner approach might be ...

A poll has comment
An article has comment

So the comments table structure could be

CommentId (UniqueIdentifier)
CommentType (PollTypeComment, ArticleTYpeComment)

and CommnetId could be foreign key in tables article and poll. This would allow you to easily query both ways. The previous experts suggestion may not be the most ideal if you were searching to find out to which article or poll a  comment was related to. You would have to search both the table to find the associated poll or article, however this way it gives you more flexibility
szacksConnect With a Mentor Commented:
1) You can use a GUID which is unique, but not in any order.

2) Create a table called KeyNumber with 2 fields an Identity field and a bit field. When you are ready to add a new row to any of your tables add a new row to the KeyNumber table and get the value of the identity field using @@identity then insert that number into the unique primary key spot.
How are Table A and Table B related? If you need the key to be unique across multiple problems, yo might want to normalize the tables. There might be a design issue here.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

What is the problem you're trying to solve that requires unique keys across tables?  AFter 15 years of database programming, I can't recall someone trying to do this.
Timbo87Author Commented:
The scenario requires that I'm going to have two tables, Articles and Polls and a single Comments table for both. I need to avoid collisions between the two tables because, obviously, I can't have a primary key of 1 in Articles and 1 in Polls because they'd both refer to the same Comments row. Any suggestions?
Anthony PerkinsCommented:
>>Any suggestions?<<
Look at szacks first suggestion using uniqueidentifiers.
Anthony PerkinsCommented:
On second thoughts szacks second suggestion is equally valid.
Scenario 1: You NEVER want a comment to be linked to BOTH an Article and a Poll.

Solution 1: Create TWO separate comment tables. There is no need to keep them in one and you quite evidently create a headache for yourself by trying to do so. DO NOT try to keep all the comments in one comment table.

Scenarion 2: You DO want a comment to be linked to BOTH an Article and a Poll.

Solution 2: Create tblComments, tblArticles and tblPolls all with unique identifiers (Primary Keys). Add Two columns to tblComments: UniqueIdArticles, and UniqueIdPolls. Each comment gets a link to the appropriate Article or Poll to which it must be attributed.
If you are keeping a sepearte table for comments that means that you probably want to have the ability for multiple comments per article and per poll. Otherwise, I would put the comments directly into the primary table.

If you keep them in the same table then it is very easy to search all comments, though if they are not related to each other that may confuse you.

It will not be confusing to query the table unless you start from the comments table and go backwards to try and figure out which record this comment belongs to. Normally you will go from either the Polls or articles table to the comments table and therefore there will be a simple link which completely ignores any comments that don't have related keys.

Whether or not this is what you want to do probably depends on your exact situation and how you will be using the data and what querying requirements you foresee.

Good point szacks.
Timbo87Author Commented:
Thanks for all the comments. Based on what I've read, here's what I'm planning. It seems to be most like danths' comment, except rather than putting a CommentID foreign key in the Articles and Polls tables, I'm putting a foreign key to the Article or Poll in the Comments table.

For the Comments table:

CommentID - primary key
CommentType - either Article or Poll
ItemID - Foreign key of the Article or Poll
...other columns...

So I could write article comment queries like this:
SELECT * FROM Comments WHERE CommentType = 'Article' AND ItemID = 1

and poll comment queries like this:
SELECT * FROM Comments WHERE CommentType = 'Poll' AND ItemID = 1

Are there any potential pitfalls with a design like this?
This would work just fine except that I would suggest putting in a clustered key with ItemId and CommentType in that order. In fact you could make that index unique. Also I would suggest a bit field for rather than text for the commenttype to keep the index small. For sure that you cannot depend on the commenttype on its own because of lack of proper distribution of the values to make it a sensible index.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.