CODEWITHSUNDEEP
sqlpostgresqlunique-constraintpostgresql-10
notme1560
notme1560

Reputation: 366

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.

Upvotes: 1

Views: 2070

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270823

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.

Upvotes: 3

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658672

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:

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:

Upvotes: 0

Related Questions