Should I use composite primary keys or not?

by

In my personal opinion you should avoid composite primary keys due to several reasons:

  1. Future changes: when you design a database you sometimes miss what in the future will become important. A significant example for this is thinking a combination of two or more fields is unique (and thus can become a primary key), whereas in the future you want to allow NULLs or other non-unique values in them. Having a single primary key is a good solid solution against such changes.

  2. Uniformity: If every table has a unique numerical ID, and you also maintain some standard as to its name (e.g. “ID” or “tablename_id”), the code and SQL referring to it is clearer (in my opinion).

There are other reasons, but these are just a few.

The main question I would ask is why not use a separate primary key if you have a unique set of fields? What’s the cost? An additional integer index? That’s not too bad.

Hope that helps.

More Related Contents:

  1. String variable in java to get and store the json array in table column
  2. How do I get a result across 2 tables
  3. How to get the insert ID in JDBC?
  4. java.util.Date vs java.sql.Date
  5. How can I get the SQL of a PreparedStatement?
  6. Design Patterns: Factory vs Factory method vs Abstract Factory
  7. SQL parser library for Java [closed]
  8. How to call a stored procedure from Java and JPA
  9. “EEE MMM dd HH:mm:ss ZZZ yyyy” date format to java.sql.Date
  10. Picking a random element from a set
  11. Running a .sql script using MySQL with JDBC
  12. Prevent SQL injection attacks in a Java program
  13. Java Date – Insert into database
  14. Hibernate Union alternatives
  15. Most efficient conversion of ResultSet to JSON?
  16. JDBC Pagination
  17. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  18. Queries returning multiple result sets
  19. Class.forName(JDBC_DRIVER) no longer needed?
  20. Name database design notation you prefer and why?
  21. How do you query object collections in Java (Criteria/SQL-like)?
  22. Getting events from a database
  23. Java – Storing SQL statements in an external file [closed]
  24. Iterating through a List Object in JSP
  25. convert string date to java.sql.Date [duplicate]
  26. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  27. Java JDBC connection status
  28. Is MERGE an atomic statement in SQL2008?
  29. Hibernate Issue : Foreign key must have same number of columns as referenced primary key
  30. Does JPA support mapping to sql views?

Leave a Comment