Given the following tables:
CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE TABLE address ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified_name_id INTEGER NULL REFERENCES verified_name(id) );
How can I add an additional constraint that when
address.verified_name_id is not NULL, the
address must match the those on the referenced
I've tried adding the following to
FOREIGN KEY (name, email) REFERENCES verified_name(name, email)
...but that constraint is being applied even when
verified_name_id is NULL.
I'm looking for something similar to the partial index syntax with a clause like
WHERE verified_name_id IS NOT NULL, but simply appending a clause like that to the
FOREIGN KEY constraint doesn't work.
Current undesirable solution:
I can add the following constraints to
UNIQUE (name, email), UNIQUE (id, name, email)
And the following constraint to
FOREIGN KEY (verified_name_id, name, email) REFERENCES verified_name(id, name, email)
...but that creates an extra constraint on
verified_name that I'd prefer not to have (it's a valid logical constraint, but it's also superfluous and there are minor performance implications).