Skip to Main Content
  • Questions
  • is alter table drop constraint drop index syntactically valid?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tomas.

Asked: November 13, 2017 - 10:18 am UTC

Last updated: November 13, 2017 - 1:59 pm UTC

Version: 11.2.0.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm dropping unique constraint from a table and need to drop the unique index too. Yes there's lot written about this topic and what is not clear to me seems to be just documentation issue:

I found advices to use syntax
alter table ... drop constraint ... drop index

though this syntax seems not to be supported in railroad diagram - https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2103997 (KEEP INDEX and DROP INDEX are only in DROP PRIMARY KEY... and DROP UNIQUE... branches, not in DROP CONSTRAINT... branch).

Is there some expansion in railroad diagram I'm overlooking? Am I looking into wrong documentation? Or is it some undocumented feature? Shortly, can I rely on it in production?

Example of the occurences of the syntax at AskTom: https://asktom.oracle.com/pls/apex/asktom.search?tag=unique-index-200503#followup-6922829900346922650 (including linked https://jonathanlewis.wordpress.com/2012/04/19/drop-constraint/ site) or https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1232036500184#followup-426367200346607590 (uses KEEP INDEX but same from syntactical point of view)

Thanks for answer!

with LiveSQL Test Case:

and Chris said...

Well, the command runs so must be syntactically valid! ;)

In seriousness though, I think this is a doc bug. As well as the articles you've linked, "drop constraint ... drop index ..." appears in MOS too (see note 370633.1).

Interestingly, you can use this clause on non-primary/unique constraints. Though it does nothing:

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 ck_foo check (id > 0);

select index_name from user_indexes
where  table_name = 'FOO';

INDEX_NAME   
PK_FOO   

alter table foo drop constraint ck_foo drop index;

select index_name from user_indexes
where  table_name = 'FOO';

INDEX_NAME   
PK_FOO 

alter table foo drop constraint pk_foo drop index;

select index_name from user_indexes
where  table_name = 'FOO';

no rows selected


But if you're worried about this not working in production "one day", I'd go with "drop primary|unique". Which also allows you to remove this without knowing the constraint name:

alter table foo add constraint pk_foo primary key (id);

select index_name from user_indexes
where  table_name = 'FOO';

INDEX_NAME   
PK_FOO   

alter table foo drop primary key drop index;

select index_name from user_indexes
where  table_name = 'FOO';

no rows selected

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tomas Zalusky, November 13, 2017 - 12:26 pm UTC

Thanks for quick response, Chris!

>>I'd go with "drop primary|unique"<< - should I interpret it as it's safe to use

alter table foo drop unique (name) drop index;

which also works and moreover, is in official documentation?
Or is any change for the documentation to be fixed?

OT: I posted equivalent SO question before: https://stackoverflow.com/questions/47232317/is-oracle-alter-table-drop-constraint-drop-index-syntactically-valid - since I saw you also there I notify I will accept your answer if you submit it there :-).
Chris Saxon
November 13, 2017 - 1:59 pm UTC

Drop primary key|unique is the official documentation, so you can be sure it's "safe".

While drop constraint looks like a doc bug to me, it may also be an unintended side effect.

That said, it's been around a long time, so I doubt this will be disabled.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.