Is it better to use a composite primary key than to use a single primary key?

I've always avoided it because I though it was easier using a single, but I've seen in several threads that developers are using it so I'm wondering if someone could give me their openion on this topic.

In the tables below order_id and line_number is a compsite primary key. In this example I would have made a new primary key line_item_id. The reason is that if you are going to delete or edit a row , I find it easier to pass line_item_id with the url instead of order_id and line_number. Doesn't this make sense? Or is there some disadvantages with this?

create table orders (
id BIGINT not null generated by default as identity,
customer_id BIGINT,
date TIMESTAMP,
primary key (id)
)

create table line_items (
line_number INTEGER not null,
order_id BIGINT not null,
product_id BIGINT,
quantity INTEGER,
primary key (order_id, line_number)
)

This approach means using business keys (aka known as natural id's) as primary keys.
Me personally (and many people) think that it is always better to use surrogate keys
(= generated keys without business meaning) as primary keys like it was your original idea I suppose.
Tommorow I will explain you why...

The big problem with using business keys (natural id's) as primary key is that sooner or later (Murphy's law) they are object to value-changes.
For example with primary key (order_id, line_number) it could happen,
that there comes a request to renumber the line_numbers for certain orders.
As most probably you will use order lines primary keys as foreign keys in other tables,
you can imagine how hard work is will be to maintain the database in a consistent state.

Also imagine you have to export your data periodically to a extern or legacy system.
Handling primary key changes properly on data-interfaces is usually a complex thing.
With surrogate keys you get rid of this problems.

The unique disadvantage with surrogate keys, is to need more fields and indexes on database (index on primary key + indexes on natural id's) than with the other approach, but today with modern databases this is no more a problem.

There's another little disadvantage with using generated surrogate keys as primary key I must mention.
It regards the usage of 2L-cache.
A lookup on a determinate order with its business key order_id cannot be done by calling
session.load(order_id) because load requires always the primary key as parameter (session.load(order_surrogate_key) )
You have to supply a user-query with the condition on the business key.
User-queries by default don't hit the 2L-cache in contrast to session.load() which always tries to get the instance from 2L-cache.
To compensate this you must also enable hibernate.cache.use_query_cache and declare natural id's by annotation
(immutable if possible).

Good explanation!!

But what if you have a table with two foreign keys that are surrogate primary in another table. I've made an example below. Which is the better alternative?

Table A
pk_A (surrogate primary key)

Table B
pk_B (surrogate primary key)

Alternative #1
Tabel C
pk_A
pk_B
primary key(pk_A, pk_B)

Alternative #2
Tabel C
su_C (surrogate primary key)
pk_A
pk_B

If Table C is just a secondary table only representing a ManyToMany relation between A and B
without further informations then I would opt for Alternative #1

If Table C is indeed more a real entity class with its own fields then I would opt for Alternative #2

But if you have implemented Alternative #1 with a composite primary key, and then you need to add some more fields to the table, would you then add a surrogate key and implement Alternative #2?

Yes, I definitely would implement Alternative #2 with adding uniqueConstraints annotation
@Table(uniqueConstraints = { @UniqueConstraint(columnNames={"pk_A","pk_A"}) })
on entity C.

Alternative #1 would be what Hibernate offers on chapter 7.2.3 Adding columns to join tables in "Java Persistence with Hibernate".
It is good that Hibernate offers this, but personally I prefer Alternative #2 approach because:
- it clearly keeps separated concept of entity (main tables) from association mappings (secondary join tables)
- you never have to never deal with composite primary keys
- keep it simple