is postgresql nullable foreign key with cascade delete feature possible-CodePudding

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?

CodePudding user response：

You are already doing it correctly. If the foreign key column is NULL, referential integrity is not enforced for that row.

CodePudding user response：

This is possible. Just check following threads:

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