How to create an optional foreign key reference?

Question

I want to create a foreign key that maps an object by someId in this example:

@Entity
public class MyEntity {

    private int someId;

    @ManyToOne
    @JoinColumn(name = "someId", foreignKey = @ForeignKey(name="fk_id"), insertable = false, updatable = false, nullable = true)
    private MyObject obj;
}

As you see: someId creates also a foreign key reference to MyObject by the same id.

Problem: I want to be able to set someId, and at the same time a MyObject in table my_object_table may not exist.

Is that possible at all?

I someId is set but the same id does not exist in the foreign table,I'm getting the following error on persistence:

Caused by: org.postgresql.util.PSQLException: ERROR: Insert or Update in table "my_entity" violates forgein key constraint "fk_id".
  Detail: Key (some_id)=(940) is not contained within table "my_object_table".
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

Usually, it is possible to have an "optional" FK, although the details on the implementation depend on the database you are using. In `postgres` it should be possible, take a look [here](http://www.postgresql.org/message-id/o4527vgc2tl60snr5hbuv75hdob58n99n4@4ax.com). So you should not get the error you're getting. Maybe you could post the sql command for the entity creation? Also, take a look to [this SO question](http://stackoverflow.com/questions/15511899/cannot-make-manytoone-relationship-nullable) — lrnzcig, Dec 14 '15 at 12:08
Old question, but for anyone coming across this in the future... You could create a join table, so instead of having my_entity referencing my_object_table, you would have a join table my_entity_my_object_table which joined the two tables when relevant and when not relevant then it would simply not contain an entry. — StackUMan, Aug 13 '21 at 13:57

score 3 · Accepted Answer · answered Dec 14 '15 at 22:54

It doesn't make sense to have a foreign key that's sometimes enforced and sometimes not.

Usually an "optional" foreign key would be one where the table with the foreign key reference is nullable. If it's not null the foreign key constraint is enforced by checking that the referencing value exists in the referenced table. Such a constraint would not permit a MyEntity to exist with an obj column referencing a nonexisting MyObject. It would only permit a null obj column.

You seem to want a foreign key constraint that doesn't check, or doesn't care, whether the referenced value exists. I.e. allows MyEntity.obj to have a value that does not correspond to any valid MyObject. This does not make sense. It is called "not having a foreign key". It's like asking for a unique constraint where it's not always unique.

Now, perhaps you want to define a foreign key constraint that's enforced only for some subset of rows, e.g. "check the foreign key constraint only if the column soft_deleted is false". This is not currently supported by PostgreSQL for foreign keys; it'd be something like a partial unique index, but for a foreign key.

Perhaps you want to be able to temporarily violate the foreign key during a transaction, so long as it's valid again by the end of the transaction. This is possible using DEFERRABLE foreign key constraints, per the manual. I have no idea if Hibernate supports creating them or managing them, but if you define them in your database schema with SQL, using DEFERRABLE INITIALLY DEFERED, it should "just work" with Hibernate, checking the FK at commit time instead of immediately.

How to create an optional foreign key reference?

1 Answers1