I’m trying to guarantee that there are no duplicated rows for a given table on my POSTGRESQL database, under the following criteria:
Say there are three columns: column_scope, column_1 and column_2. I want to ensure that in the scope of a given column_scope’s value, there aren’t any rows that have the same value for column_1 on their column_2, and vice-versa.
That is, within the scope, the only way for a column_1 to have the same value as a column_2, is either for both of them to be NULL or for them to be the same row.

I tried adding an unique index (column_scope, column_1, column_2), however this would fail since it wouldn’t take into consideration invalid cases such as:

row_1 -> (scope_1, x, NULL);
row_2 -> (scope_1, NULL, x);

or

row_1 -> (scope_1, x, y);
row_2 -> (scope_1, y, z);

I also tried adding a constraint to the table, however I can’t seem to get the conditions right for assuring that column_1 and column_2 don’t have shared values for a given column_scope.