Re: Clustered index question

KPonn wrote: > > Hello, > > We have a table with an index "i_abcd" on an integer column. I changed it to > clustered index using alter index statement. It turned out that this column has > a referential constraint (This I didnot see before). Now I want change it back > to non clustered index. The name of this index is changed to " 336_226" and > alter index is not working. How do I do this ? What happens if I drop the> constraint and add an index i_abcd and put the constraints back on ? > > Another thing is that I see 336_226 name using dbaccess->info->index. But when > I do a dbschema on the table, it says 128_226 (not 336_226) on this column. Can > anyone comment on this - what do I need to do to change it back to non > clustered index ? > > Thanks > Keith Hmm. The engine creates indexes with odd names (a leading space) on columns in primary key and referential constraints. Unless something has gone wrong, these should be automatically dealt with through any ALTER TABLE statements that apply to their constraints. If you need to do anything to indexes with leading spaces in their names you need to set the environment variable DELIMIDENT=1 and use double quotes " xxx_yyy" around your index names. Check tables sysconstraints and sysindexes to see how these relate. As already stated, you don't need to change back to non-clustering; it will have no effect. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.co.uk/