Composite foreign key for profiles -> party_member

[novacrazy]

lantern.party_member already contains a composite primary key of (user_id, party_id), so it would be great to reference that as a composite foreign key in lantern.profiles when user_id and party_id are not-NULL. That way when the party_member entry is deleted, the (if any) per-party profile is also deleted by cascade.

However, profiles.party_id can be NULL for non-party profiles, so I'm currently unsure what exactly that foreign key declaration would look like.

I've found this article as perhaps a starting point.

[novacrazy]

Perhaps as simple as:

ALTER TABLE lantern.profiles ADD CONSTRAINT party_member_fk FOREIGN KEY (user_id, party_id)
    REFERENCES lantern.party_member (user_id, party_id) MATCH PARTIAL
    ON DELETE CASCADE ON UPDATE NO ACTION;

but I'm not sure.