RDB$FOREIGN1

Interbase generates very unfriendly names for constraints. This is a script that will make it generate more friendly ones.

/*

SUGGESTION ABOUT HOW TO CONTROL THE NAMES OF
INDICES THAT UNDERLY PRIMARY AND FOREIGN KEYS

SOME COMMENTS ABOUT THIS SCRIPT:
================================

Interbase (here: IB5.x on Wintel) generates an index every time you
declare a primary or foreign key for a table. The pk or fk is a
constraint that can be given a name, but the generated index always
defaults to a name like "RDB$PRIMARYnn" respectively "RDB$FOREIGNnn"
where nn is a number that is given by a system generator. Defining a
different name is not offered by DDL what is really regrettable.

The pk/fk constraints are stored in the RDB$RELATION_CONSTRAINTS system
table. In the RDB$CONSTRAINT_NAME column you find the constraint name of
the fk/pk and the RDB$INDEX_NAME column stores the name of the index IB
generates for you. Data about the indices is stored in RDB$INDICES and
RDB$INDEX_SEGMENTS tables. When no record in RDB$RELATION_CONSTRAINTS
references an index you can update this index's records in RDB$INDICES
and RDB$INDEX_SEGMENTS and even change an index name this way (e.g. for
user declared indexes). For indexes that are used for a relation
constraint this is prevented by a system trigger. On the other hand
updating RDB$RELATION_CONSTRAINTS seems to be generally forbidden.

To break these bounds I declare an additional before-insert trigger on
RDB$RELATION_CONSTRAINTS where I modify the default index name and
change it to a concatenation of the prefix "IDX_" and the name of the
underlying ref. constraint. Original name and substituted name are
temporarily stored in the table "hacked_indexnames". The trigger is
fired when e.g. a table is created that contains a primary or foreign
key constraint. After this has happen the RDB$RELATION_CONSTRAINTS table
holds a new record with my "IDX_xxxx" name in the RDB$INDEX_NAME column.
(Note that the constraint name must not be longer than 31-4=27 characters!)
At this moment this is an inconsistent situation, because the index
nevertheless has been stored under it's default name in RDB$INDICES and
RDB$INDEX_SEGMENTS and so the new record in RDB$RELATION_CONSTRAINTS
points to a index that doesn't exist. Yet this situation enables me to
modify the new records in RDB$INDICES and RDB$INDEX_SEGMENTS, what is
done by my stored procedure "apply_indexnames". You have to execute this
procedure every time after you've created a table or created relation
constraints in a different way. The COMMIT before an after executing
"apply_indexnames" seems to be necessary because the Interbase kernel
(does such exist?) appears to have it's own view to the system tables.

The script below demonstates this "hack".

Run it and try for example "SET PLAN;" and "SELECT * FROM a ORDER BY
a1;" and you get

PLAN (A ORDER IDX_PK_A)

rather than "PLAN (A ORDER RDB$PRIMARY1)"

You can use the hacked index names in PLAN clauses. Validating the
database reports no error and the hacked index names even survive a
g'backup/resorte. Anyway, of course I DO NOT CLAIM THIS IS A SAFE WAY TO
GO nor do I recommand using it. This is just an example that comes of what you can
do on you. Feel free to use it on an "AS_IS" basis.

Your Feedback is welcome.

Karsten Strobel
AIT GmbH - Augsburg
Germany
(03-AUG-1998)

email: strobel@ait-augsburg.de

28-OCT-1999:
Tested with IB5.6 (Wintel), still works fine

*/

CREATE DATABASE "C:\TEMP\TEST.GDB" USER "SYSDBA" PASSWORD "masterkey";

CREATE TABLE hacked_indexnames (old_name VARCHAR(31), new_name VARCHAR(31));

SET TERM ^ ;

CREATE TRIGGER rel_constr_bi FOR RDB$RELATION_CONSTRAINTS BEFORE INSERT AS
DECLARE VARIABLE new_idx_name VARCHAR(31);
BEGIN
  IF (NEW.RDB$INDEX_NAME IS NOT NULL AND
      NEW.RDB$CONSTRAINT_TYPE IN ("PRIMARY KEY","FOREIGN KEY")) THEN
  BEGIN
    new_idx_name = "IDX_"||NEW.RDB$CONSTRAINT_NAME; /* This will fail if longer than 31 chars !!! */
    INSERT INTO hacked_indexnames VALUES (NEW.RDB$INDEX_NAME, :new_idx_name);
    NEW.RDB$INDEX_NAME = new_idx_name;
  END
END
^

CREATE PROCEDURE apply_indexnames AS
DECLARE VARIABLE old_idx_name VARCHAR(31);
DECLARE VARIABLE new_idx_name VARCHAR(31);
BEGIN
  FOR
    SELECT old_name, new_name FROM hacked_indexnames
    INTO :old_idx_name, :new_idx_name
  DO
  BEGIN
    UPDATE RDB$INDEX_SEGMENTS SET RDB$INDEX_NAME = :new_idx_name WHERE RDB$INDEX_NAME = :old_idx_name;
    UPDATE RDB$INDICES SET RDB$FOREIGN_KEY = :new_idx_name WHERE RDB$FOREIGN_KEY = :old_idx_name;
    UPDATE RDB$INDICES SET RDB$INDEX_NAME = :new_idx_name WHERE RDB$INDEX_NAME = :old_idx_name;
  END
  DELETE FROM hacked_indexnames;
END
^

SET TERM ; ^

CREATE TABLE a (a1 INTEGER NOT NULL CONSTRAINT pk_a PRIMARY KEY,
a2 INTEGER);

COMMIT;
EXECUTE PROCEDURE apply_indexnames;
COMMIT;

CREATE TABLE b (b1 INTEGER NOT NULL CONSTRAINT pk_b PRIMARY KEY,
b2 INTEGER CONSTRAINT fk_b2_a REFERENCES a (a1));

COMMIT;
EXECUTE PROCEDURE apply_indexnames;
COMMIT;

ALTER TRIGGER rel_constr_bi INACTIVE;

COMMIT;

 

Share this article!

Follow us!

Find more helpful articles: