SQL constraint to check whether value doesn't exist in another table

Question

In my PostgreSQL 9.4 database, I have a table fields with a column name with unique values.

I'm creating a new table fields_new with a similar structure (not important here) and a column name as well. I need a way to constraint name values to be inserted to the fields_new not to be present in fields.name.

For example, if fields.name contains the values 'color' and 'length', I need to prevent fields_new.name from containing 'color' or 'length' values. So, in other words I need to provide that the name columns in both tables do not have any duplicate values between them. And the constraint should go both ways.

Answer 1

Only enforce constraint for new entries in fields_new

CHECK constraints are supposed to be immutable, which generally rules out any kind of reference to other tables, which are not immutable by nature.

To allow some leeway (especially with temporal functions) STABLE functions are tolerated. Obviously, this cannot be completely reliable in a database with concurrent write access. If rows in the referenced table change, they may be in violation of the constraint.

Declare the invalid nature of your constraint by making it NOT VALID (Postgres 9.1+). This way Postgres also won't try to enforce it during a restore (which might be bound to fail). Details here:

• Disable all constraints and table checks while restoring a dump

The constraint is only enforced for new rows.

CREATE OR REPLACE FUNCTION f_fields_name_free(_name text)
  RETURNS bool AS
$func$
SELECT NOT EXISTS (SELECT 1 FROM fields WHERE name = $1);
$func$  LANGUAGE sql STABLE;

ALTER TABLE fields_new ADD CONSTRAINT fields_new_name_not_in_fields
CHECK (f_fields_name_free(name)) NOT VALID;

Plus, of course, a UNIQUE or PRIMARY KEY constraint on fields_new(name) as well as on fields(name).

Related:

• CONSTRAINT to check values from a remotely related table (via join etc.)
• Function to update a status flag for validity of other column?
• Trigger vs. check constraint

Enforce both ways

You could go one step further and mirror the above CHECK constraint on the 2nd table. Still no guarantees against nasty race conditions when two transactions write to both tables at the same time.

Or you could maintain a "materialized view" manually with triggers: a union of both name columns. Add a UNIQUE constraint there. Not as rock solid as the same constraint on a single table: there might be race conditions for writes to both tables at the same time. But the worst that can happen is a deadlock forcing transactions to be rolled back. No permanent violation can creep in if all write operations are cascaded to the "materialized view".

Similar to the "dark side" in this related answer:

• Can PostgreSQL have a uniqueness constraint on array elements?

Just that you need triggers for INSERT / UPDATE / DELETE on both tables.

Answer 2

I had a similar problem where I wanted to maintain a list of items per-company, along with a global list for all companies. If the company number is 0, it is to be treated as global and a new item cannot be inserted for ANY company using that name. The following script (based on the above solution) seems to work:

drop table if exists blech;

CREATE TABLE blech (
        company int,
        name_key text,
        unique (company, name_key)
);

create or replace function f_foobar(new_company int, new_name_key text) returns bool as
$func$
select not exists (
        select 1 from blech b
        where $1 <> 0
        and b.company = 0
        and b.name_key = $2);
$func$ language sql stable;

alter table blech add constraint global_unique_name_key
check (f_foobar(company, name_key)) not valid;

insert into blech values(0,'GLOB1');
insert into blech values(0,'GLOB2');

-- should succeed:
insert into blech values(1,'LOCAL1');
insert into blech values(2,'LOCAL1');

-- should fail:
insert into blech values(1,'GLOB1');

-- should fail:
insert into blech values(0,'GLOB1');