• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 710
  • Last Modified:

Unique Key in Multiple Tables

Hello,
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?

Thanks!
0
Timbo87
Asked:
Timbo87
  • 3
  • 2
  • 2
  • +3
2 Solutions
 
szacksCommented:
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.
0
 
danthsCommented:
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.
0
 
dancebertCommented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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?
0
 
Anthony PerkinsCommented:
>>Any suggestions?<<
Look at szacks first suggestion using uniqueidentifiers.
0
 
Anthony PerkinsCommented:
On second thoughts szacks second suggestion is equally valid.
0
 
danthsCommented:
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)
CommentText

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
0
 
ala_frostyCommented:
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.
0
 
szacksCommented:
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.

0
 
ala_frostyCommented:
Good point szacks.
0
 
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?
0
 
danthsCommented:
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.
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now