Creating tables & Indexes in different dbspaces

[mynwo]

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 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 command 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

Also I would like to know whether we can create a table & index in different dbspaces in one command. We can create it in oracle, below is the code

CREATE TABLE TEST
(
COL1 NUMBER(4) CONSTRAINT PK_TEST_COL1
PRIMARY KEY
USING INDEX TABLESPACE OFMIDX,
COL2 varchar2(4),
COL3 date )
TABLESPACE USERS

Can we do the same in informix, if yes, please give me the syntax. Thanks in advance

Best Regards,

lloyd

 

[appi]

so far I know it is not possible. You can create the index only with the "create index" statement. Primary keys or constraints are valid, but no index.
rgds
Uwe