Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer
    
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. –  Jim Stewart Dec 5 '13 at 17:52
    
@JimStewart: Theory and advice is clean and easy. Life is messy. :) –  Erwin Brandstetter Dec 5 '13 at 22:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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