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

1 Answer 1

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.

4
  • 1
    I appreciate the purist view regarding normalized schemata, but there are other concerns at play. Application code complexity, supporting existing application code, and read performance are just three of them. These are dumbed-down tables for question purposes, and the use case is less-trivial. Thanks for the documentation pointer on MATCH SIMPLE. I hadn't realized that was how it worked. That satisfies my requirement. Dec 5, 2013 at 17:52
  • @JimStewart: Theory and advice is clean and easy. Life is messy. :) Dec 5, 2013 at 22:37
  • In the normalized schema, wouldn't it be more expensive to SELECT name, email FROM address since now it has to look in two tables on disk? i.e. in another similar data model where integrity isn't this important but it does get queried a lot, would the denormalized model be the better choice?
    – davidtgq
    Oct 12, 2017 at 15:46
  • @dtgq: May be. "Better" depends on the total sum of requirements. Denormalized models may seem simple but lead to complications with data integrity. Also, if redundant storage makes tables much bigger, overall performance may even be inferior. Oct 17, 2017 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.