Re: More ADD CONSTRAINT behaviour questions

On Tue, 10 Jul 2001, Christopher Kings-Lynne wrote:

> 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?

Well, either works, but if you promote, you should have a way
to keep track of the fact you did so, because dropping the
constraint shouldn't drop the index then but demote it.
I'm less sure about what the correct behavior would be for adding
primary keys (if you added a primary key on a unique index and
then dropped the primary key, do you end up with a normal 
unique at the end?)

> 2. A non-unique index is already defined over (b, a)
> 
>     - As above?
I agree with Tom for 2/4/6, since the indexes are different
for planning purposes.

> 3. A primary index is already defined over (a, b)
> 
>     - ERROR: unique already implied by primary?

Seems reasonable.  Maybe errors like:ERROR: Primary key <name> already defined on test(a,b)ERROR: Unique constraint
<name>already defined on test(a,b)