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.

jbranchaud · Accepted Answer · 2024-10-19 23:14:15Z

3

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.

edited Oct 19, 2024 at 23:14

jbranchaud

6,1279 gold badges49 silver badges75 bronze badges

answered Dec 19, 2017 at 15:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

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

notme1560 Over a year ago

thanks for the help. might want to delete the unique constraint line in your code block though...

Erwin Brandstetter · Accepted Answer · 2017-12-19 15:31:12Z

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

Collectives™ on Stack Overflow

Use functions in unique constraint on multiple columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related