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

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

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

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