When adding unique keys:

* If you do this, you get two unique keys (7.0.3):

create table test (int4 a, int4 b);
create unique index indx1 on test(a, b);
create unique index indx2 on test(a, b);

Then you get this:

          Table "test"
 Attribute |  Type   | Modifier
-----------+---------+----------
 a         | integer |
 b         | integer |
Indices: asdf,
         asdf2

* If you do this, you only get two unique keys (7.0.3):

create table test (a int4, b int4, unique(a, b), unique(a, b));

Then you get this:

          Table "test"
 Attribute |  Type   | Modifier
-----------+---------+----------
 a         | integer |
 b         | integer |
Indices: test_a_key,
         test_a_key1

* So, does this mean that my ALTER TABLE/ADD CONSTRAINT code should happily
let people define multiple unique indices over the same columns?

* As a corollary, should it prevent people from adding more than one primary
key constraint?

Chris

ps. I know I only tested these on 7.0.3 - but I assume HEAD has similar
behaviour?


Mon, 9 Jul 2001 09:26:33 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
ADD CONSTRAINT behaviour question
> You assume wrong.
>
> It's a bad idea to try to develop backend code against back releases.

My bad.  I'm at work at the moment and I tried it out here to rejig my
memory before posting.  I do remember testing it on HEAD at home and the
create table (.., unique, unique) doesn't duplicate.

Don't worry - it's being developed on HEAD - I was just trying to get out of
figuring out how to detect an already existing index across specified
columns.  It's complicated.

Chris


Mon, 9 Jul 2001 09:49:03 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: ADD CONSTRAINT behaviour question 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> ps. I know I only tested these on 7.0.3 - but I assume HEAD has similar
> behaviour?

You assume wrong.

It's a bad idea to try to develop backend code against back releases.

			regards, tom lane

Sun, 08 Jul 2001 21:50:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ADD CONSTRAINT behaviour question 