?
Solved

Unique Key in Multiple Tables

Posted on 2004-07-31
12
Medium Priority
?
697 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

801 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