December 23, 2008 at 10:36 am
hi.
i am a total application side developer who has recently taken up a job that demands much more sql then i have ever
cared for...
So now i have come up with a design where a table has a composite key as primary key and all the members of the
primary key are actually foreign keys (only 2 keys are there right now as part of composite keys).
Does anyone know of any potential pitfall that I may run into later with this? Or these kind of primary keys are
pretty common (and maybe recommended....) in the kingdom of DBAs?
Sunil
December 23, 2008 at 11:00 am
It is common to have tables that have multiple columns that create the uniqueness of row. Some SQL Server DBA's like to use these as composite primary keys, others would use a composite unique index/constraint and add and identity column as the primary key.
The only pifall, IMO, to having composite primary key is that you need all the values to insure updates and deletes are affecting the correct row.
I look forward to following this thread as others more knowledgeable than myself chime in.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 23, 2008 at 11:04 am
It sounds like you are creating a join table to break up a many to many join. the best example of this I can think of would be from the old sql 2000 pubs sample database.
Think of the relation between the titles and authors tables. 1 book title could have more than one author. 1 author could write more than one book, so you have a many to many relation.
You would break that many to many with the titleauthors table.
The Titles table hold all data about a specific title, not related to the author.
the authors table holds all data about a specific title not related to the author.
The titleauthor table holds data specific to the book that deals with the author and the title as a single entity. The primary key of titleauthor is a composite key made up of 2 FK contraints back to the titles and authors table.
So yes, this is done mostly to keep the tables normalized. Follow your normalization rules and you should be ok.
-Luke.
December 23, 2008 at 11:10 am
You are VERY close to what I am doing. I have a situation where one partner can be part of many partnerships.
And one partnership can have any number of partners...
So I thought of creating an ownership table ....
That should correlate owners(partners) with partnerships....
This is reassuring... 🙂
December 23, 2008 at 11:16 am
Jack Corbett (12/23/2008)
It is common to have tables that have multiple columns that create the uniqueness of row. Some SQL Server DBA's like to use these as composite primary keys, others would use a composite unique index/constraint and add and identity column as the primary key.The only pifall, IMO, to having composite primary key is that you need all the values to insure updates and deletes are affecting the correct row.
I look forward to following this thread as others more knowledgeable than myself chime in.
Ok - I was in the same dilemma. Should I use the composite key as the primary key?? Or should I use an identity column as primary? I see no particular benefit of one over another except may be it would be faster to do a search on table using identity key as primary key.
Now that I think more about it.... can i have a clustered index on a composite key like that ?
December 23, 2008 at 11:17 am
A good reason to include an identity column (or some other type of column) for a primary key would be if you needed to have a foreign key from another table to this one.
If you had a composite key and wanted to have an FK from another table you would need to have all of the key fields in the other table rather than just one key field.
Other than that, I typically do not add the additional identity column.
December 23, 2008 at 11:26 am
December 23, 2008 at 11:32 am
Like Michael said, the only time I use an additional int PK is in times when I'd need to store a multi column PK in another table as a FK reference. It ends up cutting down on the space you need in other tables and such. The tradeoff is that you now have your unique contrainst and index to maintain as well as your PK index and contrainsts. This is somewhat of a religious debate, and you will see people swearing up and down to only do it one way vs. another. Celko and the like will always say keep it as composite, and others will say not to.
In the end it all boils down to what makes the most sense and what works best for your environment. IF you choose to create a surogate key column like an int, make sure it can hold enough values (you might need a bigint depending on the size of your table?) and make sure you create the unique constraint on your other two columns to keep your integrity.
-Luke.
December 23, 2008 at 12:08 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply