is postgresql nullable foreign key with cascade delete feature possible

Question

Is it possible to declare table column that is used as foreign key, but can be null?

I have a table messages that contains user_uuid as foreign key reference to user_profiles table.

SELECT * FROM messages;

id   user_uuid                              message
--   ---------                              -------
 1   f52acab5-6115-4a09-ad81-eac662292968   Hello user john smith
 2   null                                   Hello all

I want to allow nulls for broadcast messages, but if user_uuid is specified, i want to make sure it is validated against user_profile table, and also to perform cascade deletion of user messages in case when user gets deleted. Is there a way to do so by just database means?

p.s. I know the solution of making 0000000-0000-0000-0000-0000000000 fake record in a table and referencing it. But is there more civilized way?

Answer 1

This is possible. Just check following threads:

• PostgreSQL: NULL value in foreign key column

• Can foreign key references contain NULL values in PostgreSQL?

The cascade deletion should also work, because at the end the column is a valid reference.