Thread: Constraints not check indexes existence on creation.

Hi.

--- Next sequence of commans: ---

CREATE TABLE CURRENCY.CURRENCY (
ID                   integer       NOT NULL,
CODE                 varchar(17)   NOT NULL,
DESCRIPTION          text,
PRINT_SYMBOL         VARCHAR(17)   NOT NULL,
VERSION              smallint
);

create unique index PK_CURRENCY_IDX         on CURRENCY.CURRENCY(ID);
create unique index AK_CODE_CURRENCY_IDX    on CURRENCY.CURRENCY(CODE);

alter table CURRENCY.CURRENCY add constraint PK_CURRENCY primary key (ID);
alter table CURRENCY.CURRENCY add constraint AK_CODE_CURRENCY unique (CODE);


--- Produse next table ---

\d currency.currency
            Table "currency.currency"
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 id           | integer               | not null
 code         | character varying(17) | not null
 description  | text                  |
 print_symbol | character varying(17) | not null
 version      | smallint              |
Indexes: pk_currency primary key btree (id),
         ak_code_currency unique btree (code),
         ak_code_currency_idx unique btree (code),
         pk_currency_idx unique btree (id)

Why alter table commands not check index existence?
My indexes with _idx postfix similar implicit indexes.
Now it's not problem, but with tablespaces current implementation
restrict me to put indexes in separate tablespace.

Thanks.

Viacheslav N Tararin <taras@dekasoft.com.ua> writes:
> Why alter table commands not check index existence?

What do you want them to do exactly?  Error out if there's an equivalent
index in place already?  Absorb the index as the implementation of the
constraint?  I don't really like either of those ...

            regards, tom lane