OK, so just to summarize:
> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher
> Kings-Lynne
> Sent: Tuesday, 10 July 2001 9:39 AM
> To: Hackers
> Subject: [HACKERS] More ADD CONSTRAINT behaviour questions
>
>
> When someone issues this command:
>
> ALTER TABLE test ADD UNIQUE (a, b);
>
> What happens when:
>
> 1. A non-unique index is already defined over (a, b)
>
> - Either add new index or promote existing one to unique?
Promoting is in my too-hard basket, so I will simply add a new unique index?
Too bad if it slows them down, as they should know better? Should I issue a
NOTICE warning them that they have overlapping indices?
> 2. A non-unique index is already defined over (b, a)
>
> - As above?
Irrelevant as (a,b) will be handled independently of (b,a). Basically
problem ignored?
> 3. A primary index is already defined over (a, b)
>
> - ERROR: unique already implied by primary?
Done. Implemented.
> 4. A primary index is already defined over (b, a)
>
> - As above?
As per (2).
> 5. A unique index is already defined over (a, b)
>
> - ERROR: unique index already exists over keys?
Done. Implemented.
> 6. A unique index is already defined over (b, a)
>
> - As above. Technically a different index, but effect
> as far as uniqueness is concerned is identical?
As per (2).
> 7. No index exists over (a, b) or (b, a)
>
> - Create a new unique index over (a, b)?
Done.
My current code does all of the above, plus will auto-generate constraint
names, save it only looks at combinations of keys, not permutations - so if
a unique key exists on (a,b), you can't add one over (b,a). I should be
able to fix this in my next hack session tho. After that I'll check my use
of locking, then I'll submit a patch.
The other issue is that I'm not sure how much argument checking I should do
in my code, and how much I should leave for DefineIndex?
For example, if you have a table with cols 'a' and 'b' and you go ADD UNIQUE
(c), you get something like this:
ERROR: DefineIndex: Attribute 'c' does not exist.
However, this could be slightly odd error for the user of the ALTER
function. But I guess this kind of thing happens all thru the postgres
code... Another thing that I let DefineIndex handle is the ADD UNIQUE (a,a)
kind of thing.
Chris