At 06:23 AM 12/04/2006, you wrote:

>Hi,
>please bear with me since I'm a relative newbie to databases and a
>complete one to firbird.
>
>This said let's go to the problem:
>
>I should define a table where one field is *not* mandatory (so I do
>not have the "NOT NULL" clause), but (if it exists) it should be a
>reference to another table. I tried doing this via:
>
>CREATE TABLE Editors
>(
> EditorID INTEGER NOT NULL,
> Name VARCHAR(30) NOT NULL,
> Comment BLOB,
> CONSTRAINT Editors_C
> PRIMARY KEY (EditorID, Name )
>);
>
>CREATE TABLE Characters
>(
> CharacterID INTEGER NOT NULL,
> EditorID INTEGER,
> Name VARCHAR(64) NOT NULL,
> CONSTRAINT Characters_C
> PRIMARY KEY ( CharacterID, Name ),
> FOREIGN KEY ( EditorID ) REFERENCES Editors (EditorID)
>);
>
>... but I get an error stating I cannot have a FOREIGN KEY if the
>field (Characters.EditorID) is not a NOT-NULL/UNIQUE field
>("unsuccessful metadata update" / "could not find UNIQUE INDEX with
>specified columns").
>
>I think I missed some important point somewhere,

Yes, you did. It's actually not an error that is peculiar to
Firebird, but a relational design error.

You made the PK of the parent table a composite of (EditorID,
Name). But you are trying to reference a foreign key to only one
element of that primary key, EditorID. EditorID is neither the
primary key, nor is it constrained by a UNIQUE constraint. Foreign
keys cannot reference anything else.

There should be no reason to include Name in the PK. One Editors
entity presumably instantiates on and only one name. What you really
want here is to make EditorID your primary key. You probably don't
want a unique constraint on Name, though, since there could well be
two different editors with the same name. The EditorID is sufficient
to make them distinct.

>but I have some problems finding my way in the documentation available online.
>
>Could someone point me in the right direction??

It really depends what you are looking for. If you need to get some
guidance on relational theory, you can Google for it. There are
numerous on-line tutorials about it.

Firebird's DDL (Data Definition Language) SQL is about the most
standards-compliant SQL as you will find in the world and, again, the
Web is loaded with tutorials on it. The books you want to get
specific about Firebird are PDF files named LangRef.pdf and
DataDef.pdf, published by Borland for InterBase 6. You can find them
in a number of locations via a simple Google search. Also study the
release notes in your Firebird \doc directory, for various language
rules and syntaxes that have been added or changed over the past six years.

Go to the main Firebird website www.firebirdsql.org and click through
to the new users' guide for links to documentation of all sorts.

./heLen