Hi all,

I need to know whether i can move an index from one dbspace to other.

create table role
(
role_id integer not null ,
role_nm varchar(10) not null ,
role_desc varchar(20) not null ,
is_active "informix".boolean,
primary key (role_id) constraint role_role_id_pk
) in devdata extent size 32 next size 32 lock mode page;
revoke all on "ocslinkdev".role from "public";

I have created the above table in dbspace devdata, now i need to move
the index (i.e primary key) to another dbspace called idx_dbspace.
Please let me know whether we can do it & what is the syntax for it.

I can create an index with create index syntax in other dbspace, but i need to move the constraints i.e. primary & foreign key to other dbspace. When i created these constraints, informix internally generated an index name to them, is there a way i can change them or create a new one. Awaiting ur reply. Thanks in advance

Best Regards,

lloyd

When you want to controll the constrain of a table you have to assign an index to it.
iIe.

1. Create the flat table
2. Create the index
3. Alter the table create the constraint, pointing to the index.

Now you can simply drop the primary key constraint fro the table. Internally IDS created an index (which begins with a space - so you can't controll it)
Create a unique index in th ecorrect dbspace and alter the table.
For th ecorrect syntax, check the sql syntax guide of th ecorrect version you are using online at
http://www-3.ibm.com/software/data/i.../pubs/library/

Hi Roelwe,

Thanks for your prompt reply.

Regards,

lloyd