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 name
and email
fields on the address
must match the those on the referenced verified_name
?
I've tried adding the following to address
:
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 verified_name
:
UNIQUE (name, email),
UNIQUE (id, name, email)
And the following constraint to address
:
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).