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).
Best Answer
Proper solution
The core of the problem is the data model. In a normalized schema, you wouldn't store
name
andemail
redundantly. Could look like this:If not-yet verified names should be allowed to break the UNIQUE constraint, you can replace it with a partial UNIQUE INDEX (much like you had in mind):
Work with what you have
While stuck with your unfortunate design, the solution you already found yourself fits your requirements perfectly. A
FOREIGN KEY
with the defaultMATCH SIMPLE
behavior matches theQuoting the manual:
A (somewhat less reliable and more expensive) alternative would be a trigger on INSERT / UPDATE in
address
and trigger(s) on INSERT / UPDATE / DELETE inverified_name
.