Informix 12.10.FC14
Solaris 10  1/13

I have a legacy database with several thousand constraints with implicit indexes.

I want to change those constraints to use explicit indexes.

I'm not seeing any easy way to do that with any of the Informix administrative utilities, but, then again, this isn't something I do very often, and maybe I am missing something.

Would a good way to achieve this be to use Art's 'myexport' to generate the CREATE INDEX and CONSTRAINT scripts on (a copy of) the target database (on another instance), and then DROP all the existing constraints in the target database, followed by using dbaccess to run the index and constraint scripts generated by 'myexport'?  I'm thinking that would accomplish my objective, with minimum offline time for the target database.

Is there another way I should consider?

Thank you.

DG

David:

Yes, in v12.10 dropping the constraints and recreating the indexes followed by the constraints would be the only way and myschema can make that relatively painless:

myschema -d mydbs -t mytbl --index-file=mytrbl.idx.sql --constraint-file=mytbl.cnstr.sql /dev/null

Optionally you can use the -K option to generate longer index names or --keep-generated-names to generate short names. Examples:

Default output:

CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) ;

Note unnamed primary key constraint.

With --keep-generated-names:

CREATE TABLE "art".constr_test (
       one SERIAL(1) NOT NULL
) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

-- Index < 2539_1078> is a constraint index.  Creating normal index: P2539_1078.
CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".u2539_1163;

Note the short generated constraint name.

With -K:

CREATE UNIQUE INDEX "art".constr_test_pk ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".constr_test_pk;


Art

Informix 12.10.FC14
Solaris 10  1/13

I have a legacy database with several thousand constraints with implicit indexes.

I want to change those constraints to use explicit indexes.

I'm not seeing any easy way to do that with any of the Informix administrative utilities, but, then again, this isn't something I do very often, and maybe I am missing something.

Would a good way to achieve this be to use Art's 'myexport' to generate the CREATE INDEX and CONSTRAINT scripts on (a copy of) the target database (on another instance), and then DROP all the existing constraints in the target database, followed by using dbaccess to run the index and constraint scripts generated by 'myexport'?  I'm thinking that would accomplish my objective, with minimum offline time for the target database.

Is there another way I should consider?

Thank you.

DG

Oops, forgot to mention in v14.10 you can rename such indexes and constraints:

$ dbaccess art -

Database selected.

> rename index 2539_1078 to new_contr_index;

Index renamed.

CREATE UNIQUE INDEX "art".new_contr_index ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

David:

Yes, in v12.10 dropping the constraints and recreating the indexes followed by the constraints would be the only way and myschema can make that relatively painless:

myschema -d mydbs -t mytbl --index-file=mytrbl.idx.sql --constraint-file=mytbl.cnstr.sql /dev/null

Optionally you can use the -K option to generate longer index names or --keep-generated-names to generate short names. Examples:

Default output:

CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) ;

Note unnamed primary key constraint.

With --keep-generated-names:

CREATE TABLE "art".constr_test (
       one SERIAL(1) NOT NULL
) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

-- Index < 2539_1078> is a constraint index.  Creating normal index: P2539_1078.
CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".u2539_1163;

Note the short generated constraint name.

With -K:

CREATE UNIQUE INDEX "art".constr_test_pk ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".constr_test_pk;


Art

Informix 12.10.FC14
Solaris 10  1/13

I have a legacy database with several thousand constraints with implicit indexes.

I want to change those constraints to use explicit indexes.

I'm not seeing any easy way to do that with any of the Informix administrative utilities, but, then again, this isn't something I do very often, and maybe I am missing something.

Would a good way to achieve this be to use Art's 'myexport' to generate the CREATE INDEX and CONSTRAINT scripts on (a copy of) the target database (on another instance), and then DROP all the existing constraints in the target database, followed by using dbaccess to run the index and constraint scripts generated by 'myexport'?  I'm thinking that would accomplish my objective, with minimum offline time for the target database.

Is there another way I should consider?

Thank you.

DG

Oops, forgot to mention in v14.10 you can rename such indexes and constraints:

$ dbaccess art -

Database selected.

> rename index 2539_1078 to new_contr_index;

Index renamed.

CREATE UNIQUE INDEX "art".new_contr_index ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

David:

Yes, in v12.10 dropping the constraints and recreating the indexes followed by the constraints would be the only way and myschema can make that relatively painless:

myschema -d mydbs -t mytbl --index-file=mytrbl.idx.sql --constraint-file=mytbl.cnstr.sql /dev/null

Optionally you can use the -K option to generate longer index names or --keep-generated-names to generate short names. Examples:

Default output:

CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) ;

Note unnamed primary key constraint.

With --keep-generated-names:

CREATE TABLE "art".constr_test (
       one SERIAL(1) NOT NULL
) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

-- Index < 2539_1078> is a constraint index.  Creating normal index: P2539_1078.
CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".u2539_1163;

Note the short generated constraint name.

With -K:

CREATE UNIQUE INDEX "art".constr_test_pk ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".constr_test_pk;


Art

Informix 12.10.FC14
Solaris 10  1/13

I have a legacy database with several thousand constraints with implicit indexes.

I want to change those constraints to use explicit indexes.

I'm not seeing any easy way to do that with any of the Informix administrative utilities, but, then again, this isn't something I do very often, and maybe I am missing something.

Would a good way to achieve this be to use Art's 'myexport' to generate the CREATE INDEX and CONSTRAINT scripts on (a copy of) the target database (on another instance), and then DROP all the existing constraints in the target database, followed by using dbaccess to run the index and constraint scripts generated by 'myexport'?  I'm thinking that would accomplish my objective, with minimum offline time for the target database.

Is there another way I should consider?

Thank you.

DG

Hi Art, David,

I think it was 12.10.xC8 which introduced this?

And by renaming such implicit, internally named index to a real name (without leading space), you're making the index explicit (and standalone) and protecting it against being dropped with dropping the constraint it was tied to.

HTH,
 Andreas

Oops, forgot to mention in v14.10 you can rename such indexes and constraints:

$ dbaccess art -

Database selected.

> rename index 2539_1078 to new_contr_index;

Index renamed.

CREATE UNIQUE INDEX "art".new_contr_index ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

David:

Yes, in v12.10 dropping the constraints and recreating the indexes followed by the constraints would be the only way and myschema can make that relatively painless:

myschema -d mydbs -t mytbl --index-file=mytrbl.idx.sql --constraint-file=mytbl.cnstr.sql /dev/null

Optionally you can use the -K option to generate longer index names or --keep-generated-names to generate short names. Examples:

Default output:

CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) ;

Note unnamed primary key constraint.

With --keep-generated-names:

CREATE TABLE "art".constr_test (
       one SERIAL(1) NOT NULL
) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

-- Index < 2539_1078> is a constraint index.  Creating normal index: P2539_1078.
CREATE UNIQUE INDEX "art".P2539_1078 ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".u2539_1163;

Note the short generated constraint name.

With -K:

CREATE UNIQUE INDEX "art".constr_test_pk ON "art".constr_test (
       one ASC
) USING btree IN datadbs_1;

ALTER TABLE "art".constr_test ADD CONSTRAINT PRIMARY KEY (
       one
) CONSTRAINT "art".constr_test_pk;


Art

Informix 12.10.FC14
Solaris 10  1/13

I have a legacy database with several thousand constraints with implicit indexes.

I want to change those constraints to use explicit indexes.

I'm not seeing any easy way to do that with any of the Informix administrative utilities, but, then again, this isn't something I do very often, and maybe I am missing something.

Would a good way to achieve this be to use Art's 'myexport' to generate the CREATE INDEX and CONSTRAINT scripts on (a copy of) the target database (on another instance), and then DROP all the existing constraints in the target database, followed by using dbaccess to run the index and constraint scripts generated by 'myexport'?  I'm thinking that would accomplish my objective, with minimum offline time for the target database.

Is there another way I should consider?

Thank you.

DG