Is there Such Thing As The Opposite of Unique Constraint

Question

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.

Just put your allowed combinations in another table, and point a foreign key at it — Nick Barnes
– Nick Barnes, Commented Mar 5, 2019 at 8:22
@Colin'tHart: FKs in Postgres need to reference something UNIQUE, so that won't work — Nick Barnes
– Nick Barnes, Commented 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
– jjanes, Commented Mar 5, 2019 at 12:46

jjanes · Accepted Answer · 2019-03-05 13:02:03Z

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

CL. · Accepted Answer · 2019-03-05 08:34:10Z

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ᵀᴹ
– ypercubeᵀᴹ, Commented Mar 5, 2019 at 13:26

Stack Exchange Network

Is there Such Thing As The Opposite of Unique Constraint

2 Answers 2

Hot Network Questions

Is there Such Thing As The Opposite of Unique Constraint

2 Answers 2

Related

Hot Network Questions