Unique constraint and unique index

On Thu, Aug 22, 2013 at 2:46 AM, Ivan Radovanovic <radovanovic@gmail.com> wrote:

Just to verify:
- when unique index is created row is added only to pg_index table but not
to pg_constraint table (although in fact that index is behaving like
constraint on table)

Yep.
postgres=# create table foo (a int);
CREATE TABLE
postgres=# select count(*) from pg_index;
count
-------
112
(1 row)
postgres=# select count(*) from pg_constraint;
count
-------
2
(1 row)
postgres=# create unique index aai on foo(a); -- unique index
CREATE INDEX
postgres=# select count(*) from pg_index;
count
-------
113
(1 row)
postgres=# select count(*) from pg_constraint;
count
-------
2
(1 row)

- when unique constraint is created using appropriate syntax rows are added
to tables pg_constraint and pg_index (pg_constraint with type 'u' and
referring to index with indisunique set to true)

Yep. Following last example:
postgres=# alter table foo add unique using index aai;
ALTER TABLE
postgres=# select count(*) from pg_index;
count
-------
113
(1 row)
postgres=# select count(*) from pg_constraint;
count
-------
3
(1 row)
A unique constraint refers to a unique index using conindid of pg_constraint.

Is that correct?

Yep.
--
Michael

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