CODEWITHSUNDEEP
postgresqlunique-constraint
user933161
user933161

Reputation:

Unique among two columns

Assuming Postgresql >= 10, is there a way to constrain a table to have unique values in two (or more) columns? That is, a value can only appear in one of columns. I'd like to avoid triggers as long as I can. For a single column that would be trivial.

Let's have this table:

CREATE TABLE foo (
  col1 INTEGER,
  col2 INTEGER
);

So it should be

1 2
4 3
5 7

While 8 4 would be impossible, because there is 4 3 already.

So far I figured it could be possible constrain EXCLUDE ((ARRAY[col1, col2]) WITH &&), but it seems unsupported (yet?):

ERROR:  operator &&(anyarray,anyarray) is not a member of operator family "array_ops"

This requirement is also could be seem as an empty inner-joined table (on a.col1 = b.col2). I guess I could use triggers, but I'd like to avoid them as long as I can.

P. S. Here is a related question.

Upvotes: 2

Views: 210

Answers (1)

Luke
Luke

Reputation: 2454

I'm pretty user this answer is quite close to what you're looking to achieve but, as mentioned in the answer. There's no true way to do this as it is not common practice. In programming, when something like this happens, it would be better to perform some database refactoring to find an alternative, more ideal, solution. Hope to be of any help!

Upvotes: 1

Related Questions