3

I'm new to postgres. I wonder, what is a PostgreSQL way to set a constraint for a couple of unique values (so that each pair would be unique). Should I create an INDEX for bar and baz fields?

CREATE UNIQUE INDEX foo ON table_name(bar, baz);

If not, what is a right way to do that? Thanks in advance.

Joe Shaw
  • 22,066
  • 16
  • 70
  • 92
John Doe
  • 9,414
  • 13
  • 50
  • 69

2 Answers2

4

If each field needs to be unique unto itself, then create unique indexes on each field. If they need to be unique in combination only, then create a single unique index across both fields.

Don't forget to set each field NOT NULL if it should be. NULLs are never unique, so something like this can happen:

create table test (a int, b int);
create unique index test_a_b_unq on test (a,b);
insert into test values (NULL,1);
insert into test values (NULL,1);

and get no error. Because the two NULLs are not unique.

Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21
3

You can do what you are already thinking of: create a unique constraint on both fields. This way, a unique index will be created behind the scenes, and you will get the behavior you need. Plus, that information can be picked up by information_schema to do some metadata inferring if necessary on the fact that both need to be unique. I would recommend this option. You can also use triggers for this, but a unique constraint is way better for this specific requirement.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • Thanks, yeah, creating a trigger for that is a bit harsh – John Doe May 28 '12 at 12:10
  • 2
    It's better to use a `UNIQUE` constraint. That'll create a unique index behind the scenes, but unlike creating a unique index directly it's visible as metadata in `INFORMATION_SCHEMA` and can be used by tools. Try `ALTER TABLE tname ADD CONSTRAINT bar_baz_unique UNIQUE(bar,baz);` or in your original table definition just add an extra line `CONSTRAINT bar_baz_unique UNIQUE(bar,baz);`. – Craig Ringer May 28 '12 at 23:26
  • 1
    @CraigRinger Too bad I didn't see that because my name isn't mentioned in your message. I think its really a more obvious syntax and a better way to do that, you should write an answer, not a comment. – John Doe May 29 '12 at 05:24
  • @JohnDoe Should've at-mentioned, yeah. Still, so long as people who see Pablo's answer see it, that's fine. Better still if he edits his answer to include the better style. – Craig Ringer May 29 '12 at 06:34
  • Thanks @CraigRinger: totally agree with you. – Pablo Santa Cruz May 29 '12 at 11:04