Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Dropping An Indexed Column

    I have inherited a table with dozens of columns that I no longer want. I want to drop these columns.

    So I tried
    "ALTER TABLE mydata DROP BLOCK_ID"

    and it get an error of: cannot delete a field that is part of an index. How do I get around this?

    (BLOCK_ID is the field name of my indexed column)
    (The non-indexed ones drop fine.)

  2. #2
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    First remove the column you want to drop from all the indexes that refer it. If there are indexes that refer only that column just drop those. Then you can drop the column.

    Cheers,
    Suren.

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    Yes, I get that I have to drop the index(es) -- but how do I find out which indexes this column is in?

    I'm building up to write some scripts to automatically drop a long-list of unwanted columns - how does one go about figuring out what index a field is in? And/or is there a sql way of saying "drop this column and it's indexes" ?

  4. #4
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    Well to do that the mist easiest way is to use a graphical tool that organise indexes unser each table and to go through the index and remove the coloms.

    If you are thinking of writing scripts then you should select from catalog tables such as user_indexes and user_inx_cols. I think I got the names correct.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •