how to set a constraint that sets null on only one of the fields in the composite foreign key when t-CodePudding

I have 2 postgres tables.

table one: |id|user_id|master_team_id|role_id

table two: |uuid|user_id|master_team_id|number

master_team_id in table two can be null.
user_id and master_id foreign key references the user_id and master_team_id in table one.
in order for master_team_id in table two to not be null, the user_id and master_team_id combo must exist in table one.

how do i add a constraint that sets null on only master_team_id in the composite key(user_id, master_team_id) in table two when the referenced row in table one is deleted?

CodePudding user response：

in the FK constraint specify ON DELETE SET NULL

https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete_null.php

Side Note: I would suggest using adding a new column to table two called "TableOneID" that way you can know if the matching record exists or not.

CodePudding user response：

You can't do that yet.

What a coincidence. The ability to do that was committed yesterday, and (if all goes according to plan) will be included in v15, which is not due out for nearly a year.