1

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? – Michael Kutz Mar 5 at 1:50
  • 1
    Just put your allowed combinations in another table, and point a foreign key at it – Nick Barnes Mar 5 at 8:22
  • Just add a foreign key to the same table. – Colin 't Hart Mar 5 at 8:41
  • @Colin'tHart: FKs in Postgres need to reference something UNIQUE, so that won't work – Nick Barnes Mar 5 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 at 12:46
1

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()
RETURNS TRIGGER
AS $$
DECLARE
   row_count int;
BEGIN
   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;
   RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_little_trigger
BEFORE INSERT ON my_little_table
FOR EACH ROW
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). – ypercubeᵀᴹ Mar 5 at 13:26
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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