I want to be constrain my database with what is essentially the opposite of a UNIQUE constraint. I want to only be able to add a new row if some combination of fields has been added before.

Note: I am working within postgres.

Take the following datasets for example. I want a "NON UNIQUE" constraint on the following:

1, a, 1

1, a, 1

1, b, 1

I should not have been able to add the row (1, b, 1) since it has not appeared before. There are also other fields I don't want to constrain so it's not like all the entries are identical.

This may be a separate question, but I would also like to add the constraint that all rows with the same value in one column (i.e. a in the above example) have the same value in another.

So I want this to be impossible

1, a, 1

1, a, 1

0, a, 1

But this is ok

1, a, 1

1, a, 1

0, b, 1

I have looked around pretty heavily and the opposite of this is possible with unique constraints, but I can't find anything that works for this. Check constraints will only look at the row in question, and won't take into account the entire table's entries like a unique constraint would.

Is this even possible? Or is this now out of the realms of Postgres functionality, and must be done in-application.

  • Why is one valid and the other is not? Mar 5, 2019 at 1:50
  • 1
    Just put your allowed combinations in another table, and point a foreign key at it Mar 5, 2019 at 8:22
  • Just add a foreign key to the same table. Mar 5, 2019 at 8:41
  • @Colin'tHart: FKs in Postgres need to reference something UNIQUE, so that won't work Mar 5, 2019 at 8:44
  • How would you get the table populated in the first question? Is it OK if they are inserted in the same transaction, or are the existing combinations set in stone and no newcomers are allowed?
    – jjanes
    Mar 5, 2019 at 12:46

2 Answers 2


Your second goal can be achieved with an EXCLUDE constraint.

create extension btree_gist ;
alter table t add constraint adsfj exclude using gist (col2 with =, col1 with <>);

Subqueries are not allowed in a CHECK constraint, or in the WHEN clause of a trigger, but you can use them inside a trigger function, something like this:

CREATE FUNCTION my_little_trigger_function()
AS $$
   row_count int;
   row_count := (SELECT COUNT(*)
                 FROM ...
                 WHERE ... = NEW.x ...);
   IF (row_count = 0) THEN
       RAISE EXCEPTION 'I''m sorry, Dave. I''m afraid I can''t do that.';
   END IF;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_little_trigger
BEFORE INSERT ON my_little_table
EXECUTE PROCEDURE my_little_trigger_function();
  • Even though you can do this, it is not advised. You can run unto all kinds of problems (with concurrency or during restore of a backup for example). Mar 5, 2019 at 13:26

Your Answer

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

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