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?

Laurenz Albe · Accepted Answer · 2022-05-24 12:07:51Z

3

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

answered May 24, 2022 at 12:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

para · Accepted Answer · 2022-05-24 12:15:53Z

1

This is possible. Just check following threads:

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

answered May 24, 2022 at 12:15

para

1481 silver badge14 bronze badges

Collectives™ on Stack Overflow

is postgresql nullable foreign key with cascade delete feature possible

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related