Pros and cons of composite key as primary key

25 pts.
Tags:
composite key
primary key
surrogate key
I guess the title is pretty much the question. Just wondering what the pros and cons might be for using a composite key for a PK rather than a surrogate key. Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

I would start saying that a composite key is not the opposite of a surrogate key, but I guess you already know that.

A composite key is a key that is composed of more than one attribute, so a composite surrogate key could exist (although it would not make any sense). On the other hand, there are natural keys (the opposite of surrogate keys) that are not ‘composite’. So I think we should discuss the pros and cons of surrogate keys vs. natural keys (whether they are composite keys or not).

That said, <b>in my opinion</b>, the advantages of surrogate keys include:

- They are not ‘coupled’ to the business, so a change in the business does not necessary means changes to your primary key values.

- If the surrogate key will substitute a composite natural key, and you are going to have foreign keys on other tables referencing this key, you just need to ‘replicate’ one column to the referencing tables. This could save some space, and could make join queries simpler.

The disadvantages include:

- You need to create new columns to work as surrogate keys
- Surrogate keys don’t have any business meaning. Looking at their values doesn’t give you a clue of what they relate to.
- You might need to add additional indexes to improve performance when using the ‘natural’ columns to perform searchs.

As for the natural keys, the advantages of them are the disadvantages of the surrogate ones, and vice versa.

What’s better ? It depends.

Depending on the information you are going to store in the table, you should decide which approach works best for you.

Personally, I tend to use surrogate keys in most cases, but not always. The most important factor I consider is the possibility of change of the natural candidate keys.

—————————————————-

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JennyMack
    Hi there, Could you please be more specific? What program are you referring to? The more information you can provide, the better the community can answer your question. Thanks, Jenny Community Manager
    4,280 pointsBadges:
    report
  • Bdpowell
    Sorry, I'm studying database design and SQL. Hopefully will be working with SQL Server in the future. I promise this is not part of an assignment. It just wasn't covered clearly in the online course or texts I'm reading and thought maybe your community could share some insight. Thanks!
    25 pointsBadges:
    report
  • carlosdl
    It's ok. The answer to this question is program independant. Regards,
    68,430 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following