Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why are composite keys discouraged in hibernate?

This is from Hibernate official tutorial:

There is an alternative <composite-id> declaration that allows access to legacy data with composite keys. Its use is strongly discouraged for anything else.

Why are composite keys discouraged? I am considering using a 3-column table where all of the columns are foreign keys and together form a primary key that is a meaningful relationship in my model. I don't see why this is a bad idea, espicially that I will be using an index on them.

What's the alternative? Create an additional automatically generated column and use it as a primary key? I still need to query my 3 columns anyways!?

In short, why is this statement true? and what's the better alternative?

like image 674
Isaac Avatar asked Jan 01 '13 18:01

Isaac


People also ask

Are composite keys slower?

Having that composite primary key slows down SELECT s a tiny bit, though the effect is pretty much negligible and not worth worrying about. Having those columns indexed at all slows down your INSERT s, and you certainly are doing enough INSERT s to worry about it.

Are composite keys good?

There is no conclusion that composite primary keys are bad. The best practice is to have some column or columns that uniquely identify a row. But in some tables a single column is not enough by itself to uniquely identify a row. SQL (and the relational model) allows a composite primary key.

Why are composite keys important?

Composite keys in SQL prove to be useful in those cases where you have a requirement of keys that can uniquely identify records for better search purposes, but you do not possess any single unique column. In such cases, you must combine multiple columns to create a unique key.

Can we have hibernate entity without primary key?

Hibernate requires that entity tables have primary keys. End of story. 50k records is simply not that many when you're talking about a database.


1 Answers

They discourage them for several reasons:

  • they're cumbersome to use. Each time you need to reference an object (or row), for eexample in your web application, you need to pass 3 parameters instead of just one.
  • they're inefficient. Instead of simply hashing an integer, the database needs to hash a composite of 3 columns.
  • they lead to bugs: developers inevitably implement the equals and hashCode methods of the primary key class incorrectly. Or they make it mutable, and modify their value once stored in a HashSet or HashMap
  • they pollute the schema. If another table needs to reference this 3-column table, it will need to have a 3 columns instead of just one as a foreign key. Now suppose you follow the same design and make this 3-column foreign key part of the primary key of this new table, you'll quickly have a 4-column primary key, and then a 5-column PK in the next table, etc. etc., leading to duplication of data, and a dirty schema.

The alternative is to have a single-column, auto-generated primary key, in addition to the other three columns. If you want to make the tuple of three columns unique, then use a unique constraint.

like image 106
JB Nizet Avatar answered Sep 20 '22 00:09

JB Nizet