DDL for Table primary/unique keys was not correct when based on other indexes

[Robert Mackovik]

Hello,
this bug (release notes: DDL for Table primary/unique keys was not correct when based on other indexes) is still not fixed.

Correct DDL generated by Oracle SQL Developer:

ALTER TABLE "J_PNP_TAB"."POD_LEKAR" ADD CONSTRAINT "PK_POD_LEKAR" PRIMARY KEY ("CISLO_PODANI", "LEKAR_ID")
USING INDEX "J_PNP_TAB"."PK_POD_LEKAR" ENABLE;

DDL preview generated by PL/SQL Developer Beta:

alter table POD_LEKAR
add constraint PK_POD_LEKAR primary key (CISLO_PODANI, LEKAR_ID)
using index;

Compare user objects generated DDL script:

alter table POD_LEKAR
add constraint PK_POD_LEKAR primary key (CISLO_PODANI, LEKAR_ID);

The name of the index has to be present in DDL if it was used to create the constraint. Otherwise, the result on target DB after applying DDL script can be wrong.

You can find it @data dictionary:

select INDEX_OWNER, INDEX_NAME
from user_constraints
where constraint_name = '&PK_NAME';

I hope it will be fixed in next version

Regards,
Robert

 

[Marco Kalter]

It seems that this should have been fixed. We'll check it out!

 

[Marco Kalter]

The name of the index has to be present in DDL if it was used to create the constraint. Otherwise, the result on target DB after applying DDL script can be wrong.

The default index name is the same as the constraint name. Can you provide an example of such an incorrect index name?

 

[Robert Mackovik]

This is not incorrect. Index name can be the same as constraint name. It is the rule in my team.

 

[Robert Mackovik]

Hey,
I see I have to provide some example. Here it is:

CREATE TABLE pod_lekar (
cislo_podani VARCHAR2 (20 CHAR) NOT NULL ,
lekar_id NUMBER (15) NOT NULL ,
jmeno VARCHAR2 (200 CHAR) NOT NULL ,
adresa NUMBER (9) NOT NULL ,
telefon VARCHAR2 (50 CHAR) ,
email VARCHAR2 (254 CHAR) ,
zmena TIMESTAMP NOT NULL);

CREATE UNIQUE INDEX pk_pod_lekar ON pod_lekar (cislo_podani ASC, lekar_id ASC);

ALTER TABLE pod_lekar ADD CONSTRAINT pk_pod_lekar PRIMARY KEY ( cislo_podani, lekar_id ) USING INDEX pk_pod_lekar;

PL/SQL Developer is not able to reproduce this script via View SQL or Compare User Objects - no index name is used in last command.
If index name is not used, the result of running of some scripts on target database can be wrong, because the database is responsible to choose one of existing indexes to support the constraint, if no index name is mentioned. If there are more indexes on the table, the result on target database can be wrong. So it is very important to reproduce used scripts precisely. Eg. Oracle SQL Developer can do it very well.

 

[Marco Kalter]

We'll investigate.

 

[Robert Mackovik]

Hey Marco, any news regarding this bug?

 

[Marco Kalter]

The default index name is the primary key name, so the index name can be omitted in this case. The index name is only specified if differs from the primary key name.

 

[Robert Mackovik]

If the name of the index is different from the name of the constraint, your script is working well. But if the names are the same, there is no create index command in your script, and index is implicitly created by "alter table add constraint" command. This is not the same, implicitly created indexes are not the same as those explicitly created (eg. they disappear when you disable the constraint).

Btw, via Compare user objects functionality, generated DDL script has no using index clause.

If PLSQL Developer logic has been built on difference of the index and constraint name, such logic is wrong.

Index can be implicit (created by alter table add key command) or explicit (created by create index command).
If key is supported by explicit index, that index name should be present in command, otherwise DB is responsible to choose the right index (and there can be many indexes on the table) to support the constraint when constraint is created. And it is not done via object names, but DB considers columns present in those indexes.