Solved

Unique Key in Multiple Tables

Posted on 2004-07-31
12
649 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 100 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 6

Accepted Solution

by:
danths earned 400 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Getting certain data from a string 1 24
Link SQL table to Webpage 9 38
SQL JOIN 6 35
SQL Server creating a temp table 7 38
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now