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

出自VFP Wiki

（修訂版本間差異）
跳轉到: 導航, 搜尋
小 (vxzc)
第1行： 第1行：
-
By Douglas Tosi (douglas@improart.com.br)
+
''Italic text''[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 申请ICP经营许可证]
 +
http://www.bjicp.com/images/braintemp_1.jpg{nid GFY}
-
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 申请ICP经营许可证]
 +
http://www.bjicp.net/images/logo.jpg {nid GFY}
-
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 申请ICP经营许可证]
-
4- Recreate foreign keys using the the FKs name to name its index (as
+
http://www.bjicp.org/image/s2.gif{nid GFY}
-
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>
+
-
[http://www.hg-fix.com/ 数据恢复]
+
-
[http://www.hg-fix.com/ 硬盘数据恢复]
+
-
[http://www.hg-fix.com/ 磁带数据恢复]
+
-
[http://www.hg-fix.com/ raid数据恢复]
+
-
[http://www.hg-fix.com/ 磁盘阵列数据恢复]
+
-
[http://www.hg-fix.com/diskrecover.htm 数据恢复]
+
-
[http://www.hg-fix.com/taperecover.htm 数据恢复]
+
-
[http://www.hg-fix.com/raidrecover.htm 数据恢复]
+
-
[http://www.hg-fix.com/ 数据修复]
+
-
[http://www.hg-fix.com/ 硬盘数据修复]
+
-
[http://www.hg-fix.com/ 磁带数据修复]
+
-
[http://www.hg-fix.com/ raid数据修复]
+
-
[http://www.hg-fix.com/diskrecover.htm 数据修复]
+
-
[http://www.hg-fix.com/raidrecover.htm 数据修复]
+
-
[http://www.hg-fix.com/taperecover.htm 数据修复]
+
-
[http://www.hg-fix.com/ 磁盘阵列数据修复]
+

在2005年4月3日 (日) 03:25所做的修訂版本

Italic textICP代办 ICP ICP办理 ICP证 北京ICP ICP经营许可证 申请ICP经营许可证 http://www.bjicp.com/images/braintemp_1.jpg{nid GFY}


ICP代办 ICP ICP办理 ICP证 北京ICP ICP经营许可证 申请ICP经营许可证 http://www.bjicp.net/images/logo.jpg {nid GFY}

ICP代办 ICP ICP办理 ICP证 北京ICP ICP经营许可证 申请ICP经营许可证 http://www.bjicp.org/image/s2.gif{nid GFY}

取自"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"