Use functions in unique constraint on multiple columns

Question

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.

Gordon Linoff · Accepted Answer · 2017-12-19T23:39:30.547

2

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 ( 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.

edited Dec 19 '17 at 23:39

answered Dec 19 '17 at 15:15

Gordon Linoff

1,242,037
58
646
786

@Punknoodles . . . Unique constraints are implemented through unique indexes. Functionally it is the same. There might be some minor differences, such as whether the constraint shows up in the list of constraints on the table. – Gordon Linoff Dec 19 '17 at 15:18
1

thanks for the help. might want to delete the unique constraint line in your code block though... – notme1560 Dec 19 '17 at 15:19

Erwin Brandstetter · Answer 2 · 2017-12-19T15:31:12.753

UNIQUE constraints can only be made for one or more columns, not expressions (including functions) like you can use in UNIQUE indexes. While the constraint is implemented using an index, there are a few remaining differences. See:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

BTW:

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.

That's not what the multicolumn UNIQUE index achieves. Only the combination is unique, so (1,3) would be a dupe of (3,1), but (2,1) is still allowed, so 1 can still be in another record in the blue column. Your description does not match the constraint. At least one of both is off.

Also be aware that NULL evades your rules. So you may need NOT NULL constraints, too. See:

Create unique constraint with null columns

Use functions in unique constraint on multiple columns

2 Answers2