How to increase size of column with index-CodePudding

I want to increase size of my column (NVARCHAR2) from 32 to 64. But I am getting an error:

ORA-30556: either functional or bitmap join index is defined on the column to be modified

I found solution that I should drop index and then recreate it. But can I do it without doing it? (Maybe disabling and enable, or something like this) because for me it seems that it not make any sense when I am increasing size of column up.

But even when I try do it like I said above, sometimes I am getting another error

ORA-02429: cannot drop index used for enforcement of unique/primary key

You can strikethrough with the HTML tag ~~and the close tag~~ .

Like so

~~I suppose I need to make index unusable and then drop it and recreate all of it. But it seems to be dangerous~~ It seems it is not working even if I would like to do it

CodePudding user response：

But it seems to be dangerous

Not everything is dangerous.

The first scenario - the one you're dealing with now (increasing column size while there's a functional index on it) - should be OK.

SQL> create table test (id varchar2(2));

Table created.

Function-based index:

SQL> create index i1test on test (upper(id));

Index created.

Modifying column size won't work (as you already know):

SQL> alter table test modify (id varchar2(5));
alter table test modify (id varchar2(5))
                         *
ERROR at line 1:
ORA-30556: either functional or bitmap join index is defined on the column to
be modified

So, drop the index / alter table / create index again:

SQL> drop index i1test;

Index dropped.

SQL> alter table test modify (id varchar2(5));

Table altered.

SQL> create index i1test on test (upper(id));

Index created.

SQL>

Is it dangerous? I don't think so. Performance might suffer if table doesn't have index. If table is huge, then it'll take time to recreate the index, but - apart from performance - I guess it is OK.

Note, though, that function-based index can't be used to enforce that constraint:

SQL> create table test (id varchar2(2));

Table created.

SQL> create index i1test on test (upper(id));

Index created.

SQL> alter table test add constraint pk_test primary key (id) using index i1test;
alter table test add constraint pk_test primary key (id) using index i1test
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.


SQL>

However, when we're at index which is used to enforce primary key constraint, then yes - it might be dangerous because

primary key also enforces uniqueness so - if you drop it - someone might be able to insert/update table and violate uniqueness
primary key might be referenced by one (or more) foreign key constraints. You can't drop the primary key constraint while foreign keys exist because of
```
ORA-02273: this unique/primary key is referenced by some foreign keys
```
so you'd first have to drop foreign key constraints (you can't just disable them), which also lets someone enter invalid values into that/those column(s)

Therefore, it just depends on current situation. Sometimes it is simpler, sometimes it is complex and the way you'd deal with it depends on complexity.

BTW, do you really have to drop index used for the primary key constraint if you're modifying column's size? No:

SQL> create table test (id varchar2(2));

Table created.

SQL> create index i1test on test (id);

Index created.

SQL> alter table test add constraint pk_test primary key (id) using index i1test;

Table altered.

SQL> alter table test modify (id varchar2(5));

Table altered.

SQL>