Thread: Unique constraints and indexes.
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=#
Steve Rogerson <steve.pg@yewtc.demon.co.uk> 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
On 05/01/16 19:47, Tom Lane wrote: > Steve Rogerson <steve.pg@yewtc.demon.co.uk> 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
On Tuesday, January 5, 2016, Steve Rogerson <steve.pg@yewtc.demon.co.uk> wrote:
On 05/01/16 19:47, Tom Lane wrote:
> Steve Rogerson <steve.pg@yewtc.demon.co.uk> 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.
Steve Rogerson <steve.pg@yewtc.demon.co.uk> 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