[SOLVED] Use functions in unique constraint on multiple columns

Use functions in unique constraint on multiple columns

I have this table:

CREATE TABLE games (
    red int unique, 
    blue int unique, 
    unique (LEAST(red, blue), GREATEST(red, blue)),
    check (red <> blue)
);

When I try to make the table, it errors syntax error at or near "(". Are functions like LEAST and GREATEST not allowed when making 2 columns unique? The purpose of the least and greatest functions is so that when one ID is in red, it can't also be in another record in the blue column.

Solution

You can create an index on an expression. I'm not so sure you can put a unique constraint on expressions. But this does what you want:

CREATE TABLE games (
    red int not null, 
    blue int not null, 
    check (red <> blue)
);

create unique index unq_games_red_blue on games ( least(red, blue), greatest(red, blue) );

Note: You probably don't want red and blue to be unique individually. If there are, there is no reason for a unique constraint on the pair.