Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 927
  • Last Modified:

Why have Multiple Primary Key?

There are books that tell you, that its best practise to only have 1 primary key per table!
But then I saw AdventureWorks and other database design having multple primary key.

So the question is "Why have Multiple Primary Key?" why do it!? pro/con etc..
0
Khou
Asked:
Khou
  • 5
  • 4
  • 2
  • +5
1 Solution
 
SharathData EngineerCommented:
Every table will have only one primary key. All other will be candidate keys. One among all the  candidate keys is selected as primary key.
0
 
HoggZillaCommented:
I prefer a Primary Key to represent the uniquness of each row, not a forced uniqueness by adding an ID as the key. What is best? Partly based on performance, hugely based on preference and your entire data model. I really believe most reccomend a single Identity ID for each table because they assume most people are not smart enough to develop a logical key based on the data.
Performance is based on how you use the table. If you are inserting data in the table then a single Primary Key, let's say an auto incrementing number, will be faster than a logical key in almost every case. But that does not mean it will be faster to retrieve the data.
The logic in a single key also is based on the assumption of creation of additional indexes on your table to support querries of the data.
I say, build it smart. If the table is CustomerPhoneNumbers you it could look like this with a single primary key. The PhoneID is the key. It really means nothing. You could insert as many rows as you want for Customer 34 PhoneType 3. By the way, PhoneType 3 means what? Cell Phone? Again, that is an issue with Single PK using an ID.
PhoneID 32, Customer: 34, PhoneType 3, Number: 3829873321
or, my sugested way
Customer HoggZilla, PhoneType Cell, Number 3829873321
In this case, I know HoggZilla is a key value on the Customer's table. It also is meaningful, not like a number. The PhoneType Cell is meaningful. The primary key for this table is a combination of Customer and PhoneType.
Hope that helps.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
eventually the confusion is between:
having 1 primary key with 1 column
having 1 primary key with several columns
having 1 priamry key and another column/pair of columns being a candidate key

the "candidate key" is, like the primary key, also unique, but usually it would be less efficient to use it as primary key, either
* because it's multiple columns (more data to check for unique constraint)
* because foreign key relations would simply be hassle to have all the data to be replicated (normal forms)
* because the values are subject to be updated, which is another big hassle for updates, especially when having foreign keys



0
Technology Partners: 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!

 
dportasCommented:
Khou,

A table can and should have as many candidate keys as you need to ensure the integrity of your data and implement business rules. That's what keys are for. A candidate key may or may not be composite ("composite" or "compound" means the key has multiple attributes).

It is a long-standing convention that you should designate one of those candidate keys to be a "primary" key. This is really not an important issue but unfortunately it causes a great deal of confusion for some people. My advice is that you don't worry about it too much. Just make sure you identify all the required keys in your data models and enforce them in your database.
0
 
joeviCommented:
I agree with Hogzilla on the Natural key (vs Surrogate key) approach but that's a 'discussion' that never ends. In the end, avoiding sloppy approaches to table design will pay dividends for the life of the project and database.

Just my 2 cents.
0
 
KhouAuthor Commented:
dportas:             Khou,

A table can and should have as many candidate keys as you need to ensure the integrity of your data and implement business rules. That's what keys are for. A candidate key may or may not be composite ("composite" or "compound" means the key has multiple attributes).

It is a long-standing convention that you should designate one of those candidate keys to be a "primary" key. This is really not an important issue but unfortunately it causes a great deal of confusion for some people. My advice is that you don't worry about it too much. Just make sure you identify all the required keys in your data models and enforce them in your database.


So when should you select/assign a "candidate key" key.


0
 
dportasCommented:
Candidate keys should be identified when you create your logical data model. Choose keys based on your requirements and your analysis of whatever it is you are modelling.
0
 
KhouAuthor Commented:
example?
0
 
dportasCommented:
I'm not sure what you expect from an example but here's one:

Hardware {AssetTag, SerialNumber, Description}

This Hardware relation has two candidate keys: AssetTag and SerialNumber.

Assuming both keys are enforced in the database it isn't necessarily very important which of those keys you call the "primary" one. If one key is used as a foreign key reference elsewhere then by convention that is usually called the primary key.

Most often it makes sense to choose just one candidate key per table which will be used as a foreign key in other tables. That's what many people actually mean when they say "primary key". Obviously that convention breaks down if more than one key is used in foreign key references (unusual though that may be) or if there are no foreign key references at all.








0
 
wisc77Commented:
or in my case, of leasing buildings:
You have a building ID for each building and you have a lease ID in combination with the building ID for each lease as a unique identifier..

this allows you to see all leases based on the one building in one simple join and to get to know the IDs (which can be typically based on something.)rather than an auto incrementing number which means you don't have to do the join..  There is no real advantage to having multiple keys bar saving DB space usually.

That said the best way to set it up performance wise is generally to have an auto incrementing numeric key.
0
 
Scott PletcherSenior DBACommented:
I think you mean multiple columns in one primary key as opposed to multiple different primary keys.

"Why have Multiple-Column Primary Key?"

Because it best matches the data. Key choice should be based on the data needs.

For example, the telephone book has a multi-column primary key: (LastName, FirstName).

Thank goodness: if it were LastName only, when you found Smith, you would still have to serially search thru looking for "John" if the FirstName was not also part of the key.

For other keys, a single unique number works better. For example, customer orders almost always have just a single order number key.

In contrast, the items on the order almost always have a multi-column key, such as (orderNumber, itemNumber[or itemSeqNum]).


Fwiw, I disagree deeply on using names as a key in any table. Names change.

For example, I would *never even consider* this in a table:
Customer HoggZilla, PhoneType Cell, Number 3829873321

I would *always* replace the customer name with a customer number. All the more so since indexing names takes huge amounts of space and much, much more time to do lookups against.
0
 
KhouAuthor Commented:
@ScottPletcher: "Multiple-Column Primary Key"

Example:
User 1.* UserLoginHistory *.1 LoginHistory

In the UserLoginHistory table, would you set both UserID and LoginHistory as primarykeys?
(According to your response above, YES!)

But if you have "UserLoginHistoryID" set as primary in your UserLoginHistory table, then why would you need to use multiple column primary key? as UserLoginHistoryID is the key to find both UserID and LoginHistoryID.
0
 
KhouAuthor Commented:
composite primary key (Multiple-Column Primary Key)
0
 
KhouAuthor Commented:
( I should of address it probably when asking the question) but here is another example,


I normally do this
#1
Create table CustomerProducts
(    Customer_ProductID int identity primary key,
    CustomerID int references Customers(CustomerID) not null,
    ProductID int references Products(ProductID) not null,
    OrderLimit int not null
)


but seen a few people like to do this

#2
Create table CustomerProducts
(
   CustomerID int references Customers(CustomerID) not null primary key,    ProductID int references Products(ProductID) not null primary key,
    OrderLimit int not null
)


So I ask why? :)


0
 
dportasCommented:
Your #2 example is not legal SQL syntax. I suspect you got it wrong so please test it out and confirm what you meant by it. I expect you meant this:

CREATE TABLE CustomerProducts
(
   CustomerID INT REFERENCES Customers(CustomerID) NOT NULL,
   ProductID INT REFERENCES Products(ProductID) NOT NULL,
   OrderLimit INT NOT NULL,
   PRIMARY KEY (CustomerID, ProductID)
);

That's not two primary keys. It is ONE key with two attributes. Obviously this means something different to your example in #1 because #1 does not prevent the same (CustomerID, ProductID) from being duplicated. Your business rules and the analysis of your data should tell you which is correct. Do you wish to allow multiple rows per Customer and Product or not?
0
 
Scott PletcherSenior DBACommented:
>> CustomerProducts
Why (CustomerID, ProductID) instead of an artificial identity key? <<

Because you would end up creating an index on CustomerID (at least) anyway.  A common query would be to see all the ProductIDs and their limits for a given customer.  

Also, I think i/o will be reduced by using the natural keys rather than an artificial one, since all the ProductIDs for a given customer will be stored physically together and so can be read with much less i/o.

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now