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

出自VFP Wiki

（修訂版本間差異）
跳轉到: 導航, 搜尋
小 (vxzc)
小 (revert)
 
（2個中途的修訂版本沒有顯示）
第1行： 第1行：
-
''Italic text''[http://www.bjicp.com ICP代办]
+
By Douglas Tosi (douglas@improart.com.br)
-
[http://www.bjicp.com ICP]
+
-
[http://www.bjicp.com ICP办理]
+
-
[http://www.bjicp.com ICP证]
+
-
[http://www.bjicp.com 北京ICP]
+
-
[http://www.bjicp.com ICP经营许可证]
+
-
[http://www.bjicp.com 申请ICP经营许可证]
+
-
http://www.bjicp.com/images/braintemp_1.jpg{nid GFY}
+
-
[http://www.bjicp.net ICP代办]
+
Hello,
-
[http://www.bjicp.net ICP]
+
-
[http://www.bjicp.net ICP办理]
+
-
[http://www.bjicp.net ICP证]
+
-
[http://www.bjicp.net 北京ICP]
+
-
[http://www.bjicp.net ICP经营许可证]
+
-
[http://www.bjicp.net 申请ICP经营许可证]
+
-
http://www.bjicp.net/images/logo.jpg {nid GFY}
+
-
[http://www.bjicp.org ICP代办]
+
This is the solution I was able to get. It isn't beatiful, but works;
-
[http://www.bjicp.org ICP]
+
What does it do?
-
[http://www.bjicp.org ICP办理]
+
1- Stores primary keys, foreign keys and its indexes in temporary tables;
-
[http://www.bjicp.org ICP证]
+
2- Drop the stored primary keys and foreign keys. Their indexes will be
-
[http://www.bjicp.org 北京ICP]
+
dropped too;
-
[http://www.bjicp.org ICP经营许可证]
+
3- Recreate primary keys using its name for the index.(PK_SAMPLE will use
-
[http://www.bjicp.org 申请ICP经营许可证]
+
an index called PK_SAMPLE instead of RDB$PRIMARYxxx);
-
http://www.bjicp.org/image/s2.gif{nid GFY}
+
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>

在2005年4月18日 (一) 01:35的最新修訂版本

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 /*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*/

取自"http://wiki.sunyear.com.tw/index.php?title=FirebirdSQL/%E5%95%8F%E7%AD%94%E7%AF%87/%E6%94%B9%E8%AE%8A%E8%B3%87%E6%96%99%E8%A1%A8%E7%9A%84%E7%B4%A2%E5%BC%95%E5%90%8D%E7%A8%B1"