Why use multiple columns as primary keys (composite primary key)

by

Your understanding is correct.

You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two is guaranteed unique.

The alternative is to use a generated (non-intelligent) primary key, for example in this case OrderDetailId. But then you would not always see the relationship as easily. Some folks prefer one way; some prefer the other way.

More Related Contents:

  1. How do you like your primary keys? [closed]
  2. How to design a product table for many kinds of product where each product has many parameters
  3. designing database to hold different metadata information
  4. Sql – Indirect Foreign Key
  5. Database schema which can support specialized properties
  6. Should I have a dedicated primary key field?
  7. What is Normalisation (or Normalization)? [closed]
  8. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  9. What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]
  10. Schema for a multilanguage database
  11. Can I have multiple primary keys in a single table?
  12. Calendar Recurring/Repeating Events – Best Storage Method
  13. Same data from different entities in Database – Best Practice – Phone numbers example
  14. Relational table naming convention [closed]
  15. Sqlite primary key on multiple columns
  16. When I should use one to one relationship?
  17. Are nulls in a relational database okay? [closed]
  18. Relational database design multiple user types
  19. Facebook database design?
  20. How do you deal with polymorphism in a database?
  21. Nullable Foreign Key bad practice?
  22. Why can I create a table with PRIMARY KEY on a nullable column?
  23. Still Confused About Identifying vs. Non-Identifying Relationships
  24. Can we create multicolumn unique indexes on MS access databases?
  25. (Database Design – products attributes): What is better option for product attribute database design? [closed]
  26. Composite Primary Keys : Good or Bad?
  27. Minimum no of tables that exists after decomposing relation R into 1NF?
  28. Strings as Primary Keys in MYSQL Database [closed]
  29. Limit an sqlite Table’s Maximum Number of Rows
  30. Bill of materials database model

Leave a Comment