Search code examples
sqlindexingsql-drop

How to drop and recreate the exact index

I have to change my column data type and it is indexed. Altering the column giving me error that I need to drop the index, disabling won't work.

The question is how am I going to drop and recreate the exact index automatically? I can drop them but I have got no idea how I will be recreating them.

Cheers

Solution

  • What DBMS are you using? Some DB Management Systems have the ability to script their database objects, but the commands differ between them.

    If you can create a script, you'd do so, drop the index, change the field's datatype, save, then run the script to re-create the index.

    If you can't create a script for your index with your particular DBMS, you'll have to write down all the relevant fields and settings by hand, then drop the index, change the field's datatype, save, then manually recreate the index from your notes.