Is Oracle alter table drop constraint drop index syntactically valid?

Question

I have Oracle 11.2.0.2.0 and a table with unique constraint created by following script:

    create table foo (id varchar(26) not null, name varchar(50) not null);
    alter table foo add constraint pk_foo primary key (id);
    /**/
    alter table foo add constraint un_foo unique (name); 

I need to drop the unique constraint, which is easy:

    alter table foo drop constraint un_foo;

The trouble is: when the database is backuped in SQL Developer and then restored, then the un_foo unique index is created by explicit command placed at /**/ line:

    CREATE UNIQUE INDEX un_foo ON foo (name);

Such an explicitly-created index is not deleted by the alter command above. I realized following command works:

    alter table foo drop constraint un_foo drop index;

For primary key, similar command alter table foo drop primary key drop index is in documentation or in Oracle Developer Community discussion. Also, this answer at AskTom uses this syntax too (for keep index). However I don't see any reasoning for such syntax in railroad diagram of alter table command.

Question: is the syntax alter table foo drop constraint un_foo drop index legal? If so, based on what documentation or flow in railroad diagram? If not, why the command doesn't fail?

Thanks!

Answer 1

According to @Chris Saxon's answer to my equivalent question posted to AskTom, the syntax is confirmed as working but not present in documentation. The decision whether it is doc bug or unintended side effect is still unresolved.

I personally decided to rely on the syntax since, among other things, it is advised also in My Oracle Support.

If absolute safety (read: conforming to the documentation) is required, the only possibility is to use statement alter table foo drop unique (name) drop index;.

I summarized (not so substantial) circumstances around this issue in blogpost (in Czech).