This may appeal to NIBUs who get annoyed by the system-generated
index names for primary and foreign keys: an old hack by Karsten
Strobel that I chanced across.

Karsten demonstrates how to make user-defined PK and FK index names
for easy use in plans. Works great (I presume this method is safe?).

Using Karsten's hack, you get:

PLAN (A ORDER IDX_PK_A)

rather than:

PLAN (A ORDER RDB$PRIMARY1)

Instructions below.

Regards,
Andrew Ferguson

-----

/*

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@a...


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;