FirebirdSQL/問答篇/改變資料表的索引名稱

By Douglas Tosi (douglas@improart.com.br)


Hello,

This is the solution I was able to get. It isn't beatiful, but works;
What does it do?
1- Stores primary keys, foreign keys and its indexes in temporary tables;
2- Drop the stored primary keys and foreign keys. Their indexes will be
dropped too;
3- Recreate primary keys using its name for the index.(PK_SAMPLE will use
an index called PK_SAMPLE instead of RDB$PRIMARYxxx);
4- Recreate foreign keys using the the FKs name to name its index (as
above);

Actually I could not change an index's name, so I created an automated way
of recreating ALL FKs and PKs;

Problems: After a backup/restore I noticed duplicate indices; It seems to
be random, but in every table there is 1 or 2 indices duplicated (no mored
then that). Anyway, one of them will have the name I chose, so I can use
the plan statement;


This was all done and tested in IB5.6; Every name is stored as CHAR(31) and
that has probably changed in IB6 (sorry no IB6 here yet). A bit of work
will be needed to make it work in version 6;

I did not test running it twice against the same db; It'll probably mess
up;

I guess that is it. After a good day and a half of IB system tables I need
a rest... :)

If you have the time to test it, please let me know if it worked or not!!!
Any other comments/suggestions are also greatly appreciated. This code is
probably (if nothing goes too wrong) going to arrive to our customers in
fev/2001 as an update together with about 150 plan optimized stored
procedures;

Thanks,

dog
<code>
/*begin code*/

CREATE TABLE INDEX_SEGMENTS (
    INDEX_NAME CHAR(31),
    FIELD_NAME CHAR(31),
    FIELD_POSITION SMALLINT);

CREATE TABLE INDICES (
    INDEX_NAME CHAR(31),
    RELATION_NAME CHAR(31),
    UNIQUE_FLAG SMALLINT,
    SEGMENT_COUNT SMALLINT,
    INDEX_INACTIVE SMALLINT,
    INDEX_TYPE SMALLINT,
    FOREIGN_KEY CHAR(31));

CREATE TABLE REF_CONSTRAINTS (
    CONSTRAINT_NAME CHAR(31),
    CONST_NAME_UQ CHAR(31),
    UPDATE_RULE CHAR(11),
    DELETE_RULE CHAR(11));

CREATE TABLE RELATION_CONSTRAINTS (
    CONSTRAINT_NAME CHAR(31),
    CONSTRAINT_TYPE CHAR(11),
    RELATION_NAME CHAR(31),
    INDEX_NAME CHAR(31));

COMMIT WORK;
SET TERM ?& ;

CREATE PROCEDURE STORE_CONSTRAINTS
AS
begin
  DELETE FROM REF_CONSTRAINTS;
  DELETE FROM RELATION_CONSTRAINTS;
  DELETE FROM INDICES;
  DELETE FROM INDEX_SEGMENTS;

  INSERT INTO REF_CONSTRAINTS
  (CONSTRAINT_NAME, CONST_NAME_UQ, UPDATE_RULE, DELETE_RULE) SELECT
   RDB$CONSTRAINT_NAME, RDB$CONST_NAME_UQ, RDB$UPDATE_RULE, RDB$DELETE_RULE

  FROM RDB$REF_CONSTRAINTS;

  INSERT INTO RELATION_CONSTRAINTS
  (CONSTRAINT_NAME, CONSTRAINT_TYPE, RELATION_NAME, INDEX_NAME) SELECT
   RDB$CONSTRAINT_NAME, RDB$CONSTRAINT_TYPE, RDB$RELATION_NAME,
RDB$INDEX_NAME
   FROM RDB$RELATION_CONSTRAINTS
   WHERE RDB$CONSTRAINT_TYPE IN ('FOREIGN KEY', 'PRIMARY KEY');

  INSERT INTO INDICES
  (INDEX_NAME, RELATION_NAME, UNIQUE_FLAG, SEGMENT_COUNT, INDEX_INACTIVE,
INDEX_TYPE, FOREIGN_KEY) SELECT
   RDB$INDEX_NAME, RDB$RELATION_NAME, RDB$UNIQUE_FLAG, RDB$SEGMENT_COUNT,
RDB$INDEX_INACTIVE, RDB$INDEX_TYPE, RDB$FOREIGN_KEY
   FROM RDB$INDICES
   WHERE (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0) AND
          RDB$INDEX_NAME IN (SELECT INDEX_NAME FROM RELATION_CONSTRAINTS);

  INSERT INTO INDEX_SEGMENTS
  (INDEX_NAME, FIELD_NAME, FIELD_POSITION) SELECT
   RDB$INDEX_NAME, RDB$FIELD_NAME, RDB$FIELD_POSITION
   FROM RDB$INDEX_SEGMENTS
   WHERE RDB$INDEX_NAME IN (SELECT INDEX_NAME FROM INDICES);
end?&

CREATE PROCEDURE DROP_CONSTRAINTS
AS
  DECLARE VARIABLE CONS_NAME VARCHAR(31);
begin
  FOR SELECT CONSTRAINT_NAME
      FROM RELATION_CONSTRAINTS
      WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
      INTO :CONS_NAME DO
  BEGIN
    DELETE FROM RDB$RELATION_CONSTRAINTS
    WHERE RDB$CONSTRAINT_NAME = :CONS_NAME;
  END

  FOR SELECT CONSTRAINT_NAME
      FROM RELATION_CONSTRAINTS
      WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
      INTO :CONS_NAME DO
  BEGIN
    DELETE FROM RDB$RELATION_CONSTRAINTS
    WHERE RDB$CONSTRAINT_NAME = :CONS_NAME;
  END
end?&

CREATE PROCEDURE RECREATE_PKS
AS

  DECLARE VARIABLE CONS_NAME VARCHAR(31);
  DECLARE VARIABLE IDX_NAME VARCHAR(31);
  DECLARE VARIABLE TABLE_NAME VARCHAR(31);
  DECLARE VARIABLE IDX_UNIQUE SMALLINT;
  DECLARE VARIABLE IDX_SEGCOUNT SMALLINT;
  DECLARE VARIABLE IDX_INACTIVE SMALLINT;
  DECLARE VARIABLE IDX_TYPE SMALLINT;
  DECLARE VARIABLE IDX_FOREIGNKEY VARCHAR(31);

  DECLARE VARIABLE IDXSEG_FIELDNAME VARCHAR(31);
  DECLARE VARIABLE IDXSEG_POSITION SMALLINT;
begin
  FOR SELECT RELC.CONSTRAINT_NAME, RELC.INDEX_NAME, IND.RELATION_NAME,
             IND.UNIQUE_FLAG, IND.SEGMENT_COUNT,
             IND.INDEX_INACTIVE, IND.INDEX_TYPE, IND.FOREIGN_KEY
      FROM RELATION_CONSTRAINTS RELC
           INNER JOIN INDICES IND
                 ON IND.INDEX_NAME = RELC.INDEX_NAME
      WHERE (RELC.CONSTRAINT_TYPE = 'PRIMARY KEY') AND
            (RELC.CONSTRAINT_NAME <> RELC.INDEX_NAME)
      ORDER BY INDEX_NAME
      INTO :CONS_NAME, :IDX_NAME, :TABLE_NAME,
           :IDX_UNIQUE, :IDX_SEGCOUNT,
           :IDX_INACTIVE, :IDX_TYPE, :IDX_FOREIGNKEY DO
  BEGIN
    INSERT INTO RDB$INDICES
    (RDB$INDEX_NAME, RDB$RELATION_NAME, RDB$UNIQUE_FLAG,
     RDB$SEGMENT_COUNT, RDB$INDEX_INACTIVE, RDB$INDEX_TYPE,
RDB$FOREIGN_KEY)
    VALUES
    (:CONS_NAME, :TABLE_NAME, :IDX_UNIQUE,
     :IDX_SEGCOUNT, :IDX_INACTIVE, :IDX_TYPE, :IDX_FOREIGNKEY);

    FOR SELECT FIELD_NAME, FIELD_POSITION
        FROM INDEX_SEGMENTS
        WHERE INDEX_NAME = :IDX_NAME
        INTO :IDXSEG_FIELDNAME, :IDXSEG_POSITION DO
    BEGIN
      INSERT INTO RDB$INDEX_SEGMENTS
      (RDB$INDEX_NAME, RDB$FIELD_NAME, RDB$FIELD_POSITION)
      VALUES
      (:CONS_NAME, :IDXSEG_FIELDNAME, :IDXSEG_POSITION);
    END

    INSERT INTO RDB$RELATION_CONSTRAINTS
    (RDB$CONSTRAINT_NAME, RDB$CONSTRAINT_TYPE, RDB$RELATION_NAME,
RDB$DEFERRABLE,
     RDB$INITIALLY_DEFERRED, RDB$INDEX_NAME)
    VALUES
    (:CONS_NAME, 'PRIMARY KEY', :TABLE_NAME, 'NO',
     'NO', :CONS_NAME);
  END
end?&

CREATE PROCEDURE RECREATE_FKS
AS
  DECLARE VARIABLE CONS_NAME VARCHAR(31);
  DECLARE VARIABLE IDX_NAME VARCHAR(31);
  DECLARE VARIABLE TABLE_NAME VARCHAR(31);
  DECLARE VARIABLE IDX_UNIQUE SMALLINT;
  DECLARE VARIABLE IDX_SEGCOUNT SMALLINT;
  DECLARE VARIABLE IDX_INACTIVE SMALLINT;
  DECLARE VARIABLE IDX_TYPE SMALLINT;
  DECLARE VARIABLE IDX_FOREIGNKEY VARCHAR(31);
  DECLARE VARIABLE REFC_UPDATERULE VARCHAR(11);
  DECLARE VARIABLE REFC_DELRULE VARCHAR(11);
  DECLARE VARIABLE REFC_NAME VARCHAR(31);

  DECLARE VARIABLE IDXSEG_FIELDNAME VARCHAR(31);
  DECLARE VARIABLE IDXSEG_POSITION SMALLINT;
begin
  FOR SELECT RELC.CONSTRAINT_NAME, RELC.INDEX_NAME, IND.RELATION_NAME,
             IND.UNIQUE_FLAG, IND.SEGMENT_COUNT, IND.FOREIGN_KEY,
             IND.INDEX_INACTIVE, IND.INDEX_TYPE,
             REFC.CONST_NAME_UQ, REFC.UPDATE_RULE, REFC.DELETE_RULE
      FROM RELATION_CONSTRAINTS RELC
           INNER JOIN INDICES IND
                 ON IND.INDEX_NAME = RELC.INDEX_NAME
           INNER JOIN REF_CONSTRAINTS REFC
                 ON REFC.CONSTRAINT_NAME = RELC.CONSTRAINT_NAME
      WHERE (RELC.CONSTRAINT_TYPE = 'FOREIGN KEY')
      ORDER BY INDEX_NAME
      INTO :CONS_NAME, :IDX_NAME, :TABLE_NAME,
           :IDX_UNIQUE, :IDX_SEGCOUNT, :IDX_FOREIGNKEY,
           :IDX_INACTIVE, :IDX_TYPE,
           :REFC_NAME, :REFC_UPDATERULE, :REFC_DELRULE DO
  BEGIN
    SELECT CONSTRAINT_NAME
    FROM RELATION_CONSTRAINTS
    WHERE INDEX_NAME = :IDX_FOREIGNKEY
    INTO :IDX_FOREIGNKEY;

    INSERT INTO RDB$INDICES
    (RDB$INDEX_NAME, RDB$RELATION_NAME, RDB$UNIQUE_FLAG,
     RDB$SEGMENT_COUNT, RDB$INDEX_INACTIVE, RDB$INDEX_TYPE,
RDB$FOREIGN_KEY)
    VALUES
    (:CONS_NAME, :TABLE_NAME, :IDX_UNIQUE,
     :IDX_SEGCOUNT, :IDX_INACTIVE, :IDX_TYPE, :IDX_FOREIGNKEY);

    FOR SELECT FIELD_NAME, FIELD_POSITION
        FROM INDEX_SEGMENTS
        WHERE INDEX_NAME = :IDX_NAME
        INTO :IDXSEG_FIELDNAME, :IDXSEG_POSITION DO
    BEGIN
      INSERT INTO RDB$INDEX_SEGMENTS
      (RDB$INDEX_NAME, RDB$FIELD_NAME, RDB$FIELD_POSITION)
      VALUES
      (:CONS_NAME, :IDXSEG_FIELDNAME, :IDXSEG_POSITION);
    END

    INSERT INTO RDB$RELATION_CONSTRAINTS
    (RDB$CONSTRAINT_NAME, RDB$CONSTRAINT_TYPE, RDB$RELATION_NAME,
RDB$DEFERRABLE,
     RDB$INITIALLY_DEFERRED, RDB$INDEX_NAME)
    VALUES
    (:CONS_NAME, 'FOREIGN KEY', :TABLE_NAME, 'NO',
     'NO', :CONS_NAME);

    INSERT INTO RDB$REF_CONSTRAINTS
    (RDB$CONSTRAINT_NAME, RDB$CONST_NAME_UQ, RDB$MATCH_OPTION,
RDB$UPDATE_RULE, RDB$DELETE_RULE)
    VALUES
    (:CONS_NAME, :REFC_NAME, 'FULL', :REFC_UPDATERULE, :REFC_DELRULE);
  END
end?&

COMMIT WORK?&

EXECUTE PROCEDURE STORE_PROCEDURE ?&
COMMIT WORK?&

EXECUTE PROCEDURE DROP_CONSTRAINTS ?&
COMMIT WORK?&

EXECUTE PROCEDURE RECREATE_PKS ?&
COMMIT WORK?&

EXECUTE PROCEDURE RECREATE_FKS ?&
COMMIT WORK?&


/*end code*/

</code>