?
Solved

Unique Key in Multiple Tables

Posted on 2004-07-31
12
Medium Priority
?
706 Views
Last Modified: 2008-02-01
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
Comment
Question by:Timbo87
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 4

Assisted Solution

by:szacks
szacks earned 400 total points
ID: 11686977
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
 
LVL 6

Expert Comment

by:danths
ID: 11687661
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
 
LVL 9

Expert Comment

by:dancebert
ID: 11687783
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 15

Author Comment

by:Timbo87
ID: 11688519
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11688539
>>Any suggestions?<<
Look at szacks first suggestion using uniqueidentifiers.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11688541
On second thoughts szacks second suggestion is equally valid.
0
 
LVL 6

Accepted Solution

by:
danths earned 1600 total points
ID: 11688735
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
 
LVL 7

Expert Comment

by:ala_frosty
ID: 11691029
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
 
LVL 4

Expert Comment

by:szacks
ID: 11691483
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
 
LVL 7

Expert Comment

by:ala_frosty
ID: 11699317
Good point szacks.
0
 
LVL 15

Author Comment

by:Timbo87
ID: 11699568
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
 
LVL 6

Expert Comment

by:danths
ID: 11699914
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question