Bug? Wrong DDL for a table

[romutis]

Table definition made by PL/SQL Developer (Table -> View -> View SQL):

create table TAB_CA
(
CODUTE NUMBER not null,
NIK VARCHAR2(50) not null,
PWD VARCHAR2(64) not null,
FLGDEL CHAR(1) default 'F' not null,
REALM VARCHAR2(128),
DATINS DATE default sysdate
)
;
alter table TAB_CA
add primary key (CODUTE);
alter table TAB_CA
add check (FLGDEL IN ('F', 'T'));
alter table TAB_CA
add check (FLGDEL IN ('F', 'T'));
alter table TAB_CA
add check (FLGDEL IN ('F', 'T'));
alter table TAB_CA
add check (FLGDEL IN ('F', 'T'));
alter table TAB_CA
add check (FLGDEL IN ('F', 'T'));
alter table TAB_CA
add check (FLGDEL IN ('F', 'T'));
create unique index XAK1TAB_CA on TAB_CA (NIK);
create index XIE1TAB_CA on TAB_CA (DATINS);
create unique index XIF2TAB_CA on TAB_CA (CODUTE, NIK);
create unique index XPKTAB_CA on TAB_CA (CODUTE);

The last index XPKTAB_CA which is defined in DB as PK for this table cannon be created because PK is already defined (see text marked bold) with a random name.
Error message:
create unique index XPKTAB_CA on TAB_CA (CODUTE)

ORA-01408: such column list already indexed

The solution could be:
alter table TAB_CA
add primary key (CODUTE)
USING INDEX ...;
or more simple - change order to:
1. create table
2. create indexes
3. create constraints
(Oracle Designer generates DDL-scripts with this way).
Because if we try to add PK-constraint for a table and a index for the PK column(s) already exists - Oracle won't create new index with random name but will use already existent index as an index for PK.

Thanks a lot for Your product!
Roman.

 

[CTzen]

The DDL generated by PL/SQL Developer depends on the way objects were created. In Roman's example PK constraint was added after unique index created. And yes, in this case PL/SQL Developer should use a different order.

Here are two examples

Me:

create table t_pk (
id  number not null
);

alter table t_pk add constraint pk$pk
  primary key (id)
;

PL/SQL Dev:

-- Create table
create table T_PK
(
  ID NUMBER not null
)
tablespace USERS
;
-- Create/Recreate primary, unique and foreign key constraints
alter table T_PK
  add constraint PK$PK primary key (ID)
  using index tablespace USERS

Me:

create table t_uk (
id  number not null
);

create unique index ux$uk on t_uk(id)
;

alter table t_uk add constraint pk$uk primary key (id)
;

PL/SQL Dev (SQL statements in wrong order):

-- Create table
create table T_UK
(
  ID NUMBER not null
)
tablespace USERS
;
-- Create/Recreate primary, unique and foreign key constraints
alter table T_UK add constraint PK$UK primary key (ID);
-- Create/Recreate indexes
create unique index UX$UK on T_UK (ID)
  tablespace USERS
;