In regard to giving PK/FK indexes meaningful names:

There was a workaround for this -- I remember trying it when I first
came to Firebird, and it worked great. I think it was trigger-based
and involved modifying a system table. Unfortunately, that was
before I began keeping track of all the Firebird/Interbase tips 'n
tricks, so I don't have it handy.

Just did a quick net search and the below is what I'm referring to, I
think. Apologies for the large post but the original link was dead
so I had to pull a Google cache page (yes, could have linked to that
but might disappear tomorrow). Don't hack your system tables without
a backup!! Before this post grows any larger... here it is:

----

From:

áÐ 23:49
Subject: Re: Naming Primary keys and IndexesTo:
INTERBASE@...

Raymond Kennington wrote:

> Two reasons why the names of the primary keys and indexes need to be
> controlled:
> 1. Plans in Selects require a name for the keys and indexes.
> 2. TTables, etc., require an index name.

Raymond,
I completely agree with you. Below I send an script with some comments
that might provide a solution for this problem. I did some minor
changes
on this script since I've posted it first (a few months ago).

Here starts the script...

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

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

Interbase (here: IB5.1 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: ait-augsburg@...

*/

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;

**********************************************************************
**
* This message came from the list servers at
http://www.mers.com *
* To remove yourself from the list: send an email to
listproc@... *
* in the body of the message put: unsubscribe
INTERBASE *
* InterBase Search Engine
http://www.mers.com/searchsite.html *
**********************************************************************
**

--- In ib-support@y..., Raymond Kennington <raymondk@c...> wrote:
> Dmitry Yemanov wrote:
> >
> > Thomas,
> >
> > > What about naming the index that automatically gets generated
> > > to match the
> > > primary key name? I think this would be simple to implement.
> >
> > Already done in FB 1.5.
> >
> > Dmitry
>
> Excellent!
>
> --
> Raymond Kennington
> Programming Solutions
> W2W Team B