[GENERAL] Unique constraints and indexes.

[Steve Rogerson]

Is this a bug? I create a "unique" index, directly but it doesn't add a unique
constraint. Add a unique constraint and it adds the index and the constraint.
(pg version 9.4.5 on fedora 22, but also occurs in other versions).
Functionally I can't see a difference.

mydb=# create table test_table ( f1 bigint, f2 bigint);
CREATE TABLE
mydb=# create unique index test_table_un on test_table (f1, f2);
CREATE INDEX
mydb=# \d test_table
  Table "public.test_table"
 Column |  Type  | Modifiers
++---
 f1 | bigint |
 f2 | bigint |
Indexes:
"test_table_un" UNIQUE, btree (f1, f2)

mydb=# select conindid, contype, conname from pg_constraint where conname like
'test_table%';
 conindid | contype | conname
--+-+-
(0 rows)



-- --
mydb=# drop table test_table;
DROP TABLE
mydb=# create table test_table ( f1 bigint, f2 bigint);
CREATE TABLE
mydb=# alter table test_table add constraint test_table_un unique (f1,f2);
ALTER TABLE
mydb=# \d test_table
  Table "public.test_table"
 Column |  Type  | Modifiers
++---
 f1 | bigint |
 f2 | bigint |
Indexes:
"test_table_un" UNIQUE CONSTRAINT, btree (f1, f2)

mydb=# select conindid, contype, conname from pg_constraint where conname like
'test_table%';
 conindid | contype |conname
--+-+---
  4284073 | u   | test_table_un
(1 row)

mydb=#


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraints and indexes.

[Tom Lane]

Steve Rogerson  writes:
> On 05/01/16 19:47, Tom Lane wrote:
>> That's operating as designed.  A unique constraint needs an index,
>> but not vice versa.

> I can see that might be plausible , hence the question but as a "unique index"
> imposes as constraint they seem equivalent. What's the functional difference
> between the two situations?

There is none so far as uniqueness-enforcement is concerned, because the
index is the same either way, and that's what enforces it.

The main reason we don't automatically create a constraint for every
unique index is that not all index declarations can be represented
by SQL-standard constraints.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraints and indexes.

[Steve Rogerson]

On 05/01/16 19:47, Tom Lane wrote:
> Steve Rogerson  writes:
>> Is this a bug? I create a "unique" index, directly but it doesn't add a 
>> unique
>> constraint. Add a unique constraint and it adds the index and the constraint.
> 
> That's operating as designed.  A unique constraint needs an index,
> but not vice versa.


I can see that might be plausible , hence the question but as a "unique index"
imposes as constraint they seem equivalent. What's the functional difference
between the two situations?

Steve




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraints and indexes.

[Tom Lane]

Steve Rogerson  writes:
> Is this a bug? I create a "unique" index, directly but it doesn't add a unique
> constraint. Add a unique constraint and it adds the index and the constraint.

That's operating as designed.  A unique constraint needs an index,
but not vice versa.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unique constraints and indexes.

[David G. Johnston]

On Tuesday, January 5, 2016, Steve Rogerson 
wrote:

> On 05/01/16 19:47, Tom Lane wrote:
> > Steve Rogerson > writes:
> >> Is this a bug? I create a "unique" index, directly but it doesn't add a
> unique
> >> constraint. Add a unique constraint and it adds the index and the
> constraint.
> >
> > That's operating as designed.  A unique constraint needs an index,
> > but not vice versa.
>
>
> I can see that might be plausible , hence the question but as a "unique
> index"
> imposes as constraint they seem equivalent. What's the functional
> difference
> between the two situations?
>
>
I suspect it has to do with partial unique indexes.

David J.