CODEWITHSUNDEEP
sqlpostgresqlunique-constraint
wutzebaer
wutzebaer

Reputation: 14863

Unique null index

I have a table

CREATE TABLE mandantconfigentity
(
  id bigint NOT NULL,
  currentlegalversionnumber integer NOT NULL DEFAULT 5,
  belongsto_userid character varying(255)
)

How can i create a unique index, which ensures, that the table can have only one row where belongsto_userid is null?

This does not work:

CREATE UNIQUE INDEX unique_mandantconfig_for_null_belongsto 
    ON mandantconfigentity (1) 
 WHERE (belongsto_userid IS NULL);

Upvotes: 2

Views: 53

Answers (2)

zerkms
zerkms

Reputation: 254886

CREATE UNIQUE INDEX unique_mandantconfig_for_null_belongsto 
    ON mandantconfigentity ((1)) 
WHERE (belongsto_userid IS NULL);

So what it does - it creates a partial index over expression: that has explicitly the fixed value 1 for every row where belongsto_userid IS NULL.

The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example. The parentheses can be omitted when the expression is just a function call, as in the first example.

So extra parentheses are required by the syntax.

References:

Upvotes: 3

user_0
user_0

Reputation: 3363

The code to create index needs column name:

CREATE UNIQUE INDEX unique_mandantconfig_for_null_belongsto 
    ON mandantconfigentity (id) 
WHERE (belongsto_userid IS NULL);

Upvotes: 0

Related Questions