FirebirdSQL/問答篇/改變資料表的索引名稱
出自VFP Wiki
在2005年1月27日 (四) 07:36所做的修訂版本
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*/
