At 04:21 PM 27-06-02 +0100, you wrote:

>Yeah removing a second index on the field helped. Thanks Martijn. I'm still
>interested however in the standard code that people normally use for
>creating foreign keys, whether it be with CREATE TABLE or ALTER TABLE.
>
>I'm sure this is something many of you do on a regular basis and it would
>help me who is not a Firebird expert.

To pot it up,

it makes no difference whether you create FK constraints in CREATE TABLE or
in a later ALTER TABLE statement, *provided* you have created things in the
right order. Normally, people use ALTER TABLE to add the referential
constraints as part of a well-ordered, self-documenting scripting process
because it provides the ability to take full control of the object creation
process. Although it's possible to run the whole thing as one big script,
it's not a bad idea to have a suite of scripts that are either run
separately (manually) or as a batch, linked to one another by INPUT
statements. For example,

script 1: create the database, create the domains and generators, install
the UDFs
script 2: create the tables bare, columns only, plus any constraints that
can't be added later using ALTER (not null, default, character sets)
script 3: primary, unique and foreign key constraints
script 4: triggers
[add data and start testing]
script 5: indexes
script 6: stored procedures

A primary key constraint creates its own unique ascending index across the
PK columns; a foreign key creates its own non-unique ascending index
across the columns of the FK. Don't duplicate these indexes.

If you are going to create other indexes involving these columns (PK and/or
FK), defer creation of them until *after* the PKs and FKs are in
place. Usually, I want to be testing referential integrity in the absence
of other indexes

As development goes on, you can "top up" each script as you implement
changes. Between scripts 1 and 2 you might like to maintain a "changes"
script where you keep ALTER TABLE statements, so that you have ongoing
documentation of who did what, and why.

Standard code? you can find the syntaxes in the CREATE TABLE section of
the Language Reference. You might consider subscribing to the IB Phoenix
CD, which provides manuals with a more task-oriented approach to building
databases.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________