6

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).

Improve this question

1 Answer 1

Reset to default
13

Proper solution

The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this:

CREATE TABLE name ( name_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, verified BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (name, email) ); CREATE TABLE address ( address_id SERIAL PRIMARY KEY, name_id INT REFERENCES name(name_id) ... );

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):

CREATE UNIQUE INDEX name_verified_idx ON name(name, email) WHERE verified;

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 default MATCH SIMPLE behavior matches the

partial index syntax with a clause like WHERE verified_name_id IS NOT NULL.

Quoting the manual:

MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

A (somewhat less reliable and more expensive) alternative would be a trigger on INSERT / UPDATE in address and trigger(s) on INSERT / UPDATE / DELETE in verified_name.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.