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.