CODEWITHSUNDEEP
sqlpostgresqlindexingunique-index
user2858149
user2858149

Reputation: 11

Unique index in one table

I have a table, with the columns login, lineno, line.
Each login can have 3 lineno and 3 lines, each lineno has to be unique if the login is the same.

I've tried:

create unique index unique_Lineno on rs_line (Lineno) where login=login;

It doesn't work. Can anyone please give me some help?
I've read the http://www.postgresql.org/docs/8.0/static/indexes-unique.html but there is no sample.

Upvotes: 1

Views: 38

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656556

Your condition for the partial index: login=login doesn't do what you seem to be expecting. It always evaluates to TRUE, if login is NOT NULL, which is not helping at all for your case.

You need a multicolumn index instead:

CREATE UNIQUE INDEX rs_line_uni_idx ON rs_line (login, lineno);

Assuming your columns are NOT NULL. Else, also consider:
How to add a conditional unique index on PostgreSQL

Upvotes: 1

Related Questions