Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Abid
110 Posts |
 Posted - 2011-04-03 : 03:36:38
|
| hi, i saw a database yesterday in which there were many tables having 3 and 4 primary keys. my question is that in second normal form i have studied that all non key attributes must be completely dependent on primary key not on a subset of primary? it was further written that every table must have one primary key? so under the light of this definition its clear to not to use more than one primary keys, but if we use so why we use it? and doesn't it make the database complex? |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2011-04-03 : 04:49:17
|
| a Table can have only one primary key. Normally, it is composite from one column, max two columns, no more. If you have a primary key composite of 3 or 4 columns, i would ask my self, what is wrong with this database design.it is not mandatory for a table to have pa primary key. it will function normally as well. but introducing a primary key, you will follow a better and strict database design. and it does not complicate but rather help you to have a good primary key set on a table. |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-04-03 : 15:58:37
|
| There is no limit to the number of columns that can be in a key. The best (most natural and common) examples for 2 columns is (x, y) co-ordinates, for 3 columns is (x, y, z) co-ordinates, for 4 columns is is (x, y, z, time) co-ordinates, and for 5 columns is (x, y, z, start_time, end_time) co-ordinates. Everything exists in time and space!--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-04-03 : 19:23:05
|
quote:
Originally posted by slimt_slimt
a Table can have only one primary key. Normally, it is composite from one column, max two columns, no more. If you have a primary key composite of 3 or 4 columns, i would ask my self, what is wrong with this database design.it is not mandatory for a table to have pa primary key. it will function normally as well. but introducing a primary key, you will follow a better and strict database design. and it does not complicate but rather help you to have a good primary key set on a table.
I would disagree with stating that having more than 2 columns as the primary key is a problem. However many columns are needed to identify the uniqueness of the entity is what is required.Now, if you are stating that having a multi-column clustering key should be reconsidered - then I can agree. Not that I would necessarily add a surrogate, but I would definitely review the table and it's usage to determine if creating a surrogate key and defining that key as the clustering key would improve overall performance of the system.A simple example would be a claim system for multiple groups. In this scenario, you would have the Group table (GroupID), then the ClaimHeader table (GroupID, ClaimID), the ClaimLine table (GroupID, ClaimID, ClaimLineID) and an Adjudication table (GroupID, ClaimID, ClaimLineID, AdjudicationID).You could use an IDENTITY as a surrogate for the ClaimID - and then use that surrogate in the ClaimLine. This would make the primary key in the claim line table (ClaimID, ClaimLineID). If you used an IDENTITY in the ClaimLine table - you could then use that in the adjudication table (ClaimLineID, AdjudicationID).So, every table would then have a single column as the primary key with a FK to it's parent table. Now, with that design - if I wanted to find all claims for Group 1, where the claim line that was adjudicated was of a specific adjudication, I would have to build a query that involves all 4 tables. However, if I used the previous structure without IDENTITY columns I could just query the adjudication table to get the results.Of course, if more than 80% of my queries required accessing all 4 tables all the time it might be better using the option with IDENTITIES.I might even consider adding a surrogate key, but including all of the other columns as a unique constraint. But, that would really depend on how often the table is inserted/updated/deleted and how often it is read - and, whether or not that would cause too high a number of page splits leading to lots of fragmentation and performance issues.Jeff |
|
|
|
Abid
110 Posts |
Posted - 2011-04-03 : 23:17:58
|
quote:
Originally posted by slimt_slimt
a Table can have only one primary key. Normally, it is composite from one column, max two columns, no more. If you have a primary key composite of 3 or 4 columns, i would ask my self, what is wrong with this database design.it is not mandatory for a table to have pa primary key. it will function normally as well. but introducing a primary key, you will follow a better and strict database design. and it does not complicate but rather help you to have a good primary key set on a table.hi, i checked the database again, let me tell you how he made the tables. suppose there are 4 tables related to student right? student date of birth table, student parent info table, student personal info table and one other. so he had taken the primary keys of all three tables and put them in one big table not as a foreign key but as a primary key, so by this way he had 4 primary keys, three from the other tables and 1 of the grand table? is it right way?
|
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-04 : 09:22:29
|
| I stated my opinion about surragate vs. natural key discussion here: [url]http://mirko-marovic-eng.blogspot.com/2011/01/natural-vs-surrogate-keys-that-is.html[/url].MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
|
|
|
|
|
composite primary key