How define the dbspace where an index must to be created?
When I created a table with primary and foreing key informix define an index automatically for each key, that is fine to me but I need to define these indexes in other dbspace than root dbspace. Somebody know how to do it? Thanks. -- Luis M. Ramos V'lez GM GROUP, Puerto Rico lramos@gmgroup.com, luiggie_01@hotmail.com oficina (787)751-4343 ext. 505
Hola, Luis! ?Como Estas?
The trick here is to create the indexes before creating the primary and
foreign keys. You're probably doing something like this:
CREATE TABLE luis_table (
field1 INTEGER PRIMARY KEY,
field2 CHAR(3),
field3 CHAR(2) FOREIGN KEY REFERENCES other_table
);
To do what you're looking to do, you need to do it like this:
-----snip-----
CREATE TABLE luis_table (
field1 INTEGER,
field2 CHAR(3),
field3 CHAR(2)
);
CREATE UNIQUE INDEX luis_table_ix00 ON luis_table(field1) IN <target_dbs>;
ALTER TABLE luis_table ADD CONSTRAINT PRIMARY KEY(field1) CONSTRAINTluis_table_pk;
{
The "alter table" statement recognizes the existing index and uses it,
rather than creating its own.
}
CREATE INDEX luis_table_ix01 ON luis_table(field3) IN <target_dbs>;
ALTER TABLE luis_table ADD CONSTRAINT FOREIGN KEY (field3) REFERENCESother_table CONSTRAINT luis_table_fk1;
----snip-----
I know that this is a whole lot more SQL than the other method, but it
allows you far greater control in the long run. Also notice that I'm
explicitly naming all of my indexes and constraints. This makes them easier
to recognize and deal with later.
And PLEASE don't tell me that you're putting databases in the root dbspace.
(Very Bad Idea).
Hope this helps. Send me private e-mail if you want more details or want to
discuss this further.
Adios.
Luis M. Ramos wrote in message
<01be4013$6ea7ce80$8ff0b0b0@lramos.gmgroup.com>...
>When I created a table with primary and foreing key informix define an
>index automatically for each key, that is fine to me but I need to define
>these indexes in other dbspace than root dbspace. Somebody know how to do
>it? Thanks.
>--
>Luis M. Ramos V'lez
>GM GROUP, Puerto Rico
>lramos@gmgroup.com, luiggie_01@hotmail.com
>oficina (787)751-4343 ext. 505
>