Foreign key as the primary key or just separate surrogate primary key different from foreign key in JPA context

Question

What is the best practice to use FK as PK, or use surrogate PK, and FK just as FK in JPA context? I saw few times people said that they had to map FK as PK, because they had legacy database. So does it mean that for new tables, if you have control to create them it is better to use the following structure:

TABLE_1
-------
ID (PK)
...

TABLE_2
-------
ID (PK) 
TABLE_1_ID (FK)

instead of:

TABLE_2
-------
TABLE_1_ID (PK) and (FK)

Answer 1

In this particular case (1 to 0..1 relationship), consider merging two tables into one.

If they are split intentionally (e.g. for "vertical" partitioning), prefer the same field being both PK and FK.

Only consider adding another key if you can make it smaller¹, but balance this with the need for additional index², potential hostility towards clustering³ and a need to model diamond-shaped dependencies⁴.

---

¹ E.g. because TABLE_2.TABLE_1_ID is string and you can make the TABLE_2.ID integer.

² Every new index slows-down INSERT and can slow-down UPDATE and DELETE depending on their WHERE clause. Also, any additional data puts additional pressure on cache making it "smaller".

³ Secondary index in a clustered table needs to contain a copy of the PK and can cause a double-lookup (first for index and then for PK) when locating rows.

⁴ Using identifying relationships on both "edges" of a "diamond" may be necessary to ensure that the "bottom" of the diamond references a single "top".

Answer 2

For many to one relationships, always use the first alternative you presented.

For some one-to-one relationships, the tables may be merged with no ill effects.

Where your second alternative really becomes useful is when implementing superclass-subclass hierarchies using the class table inheritance model, as presented by Martin Fowler. In this case, you want to keep the subclass tables distinct from the superclass table, in order to reduce the number of NULLS. But the relationships are one-to-one.

By making the same key function as both a PK and an FK in the subclass tables, and by making the FK reference the matching entry in the superclass table, it makes it super easy to join the specialized data with the generalized data, when desired. This can be called "poor man's inheritance".

Answer 3

I guess you are looking for @MapsId annotation.