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!
It is in the documentation now (at least as of oracle 19).
drop_constraint_clause::=
drop (
primary key |
constraint *constraint_name* |
unique (*column*, ...)
) ... [ (drop | keep) index ]
(I did not want to copy paste the image, as I am not sure that's okay with oracle; I hope the notation is self-explanatory).