--- In firebird-support@yahoogroups.com, "zionemo" <ZioNemo@...> 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").

The problem is that Editors.EditorID is not unique. You have created a
primary key on the combination of EditorID/Name which means that I can
insert the following data:

EditorID Name
-------- --------
1 Adam
1 ZeoNemo

Now when you declare in the characters table

CharacterID EditorID Name
---------- -------- -------
1 1 'Fred'

which editors record is this characters record "pointing to".

I doubt you realise that the primary key would behave like this.

CREATE TABLE Editors
(
EditorID INTEGER NOT NULL,
Name VARCHAR(30) NOT NULL,
Comment BLOB,
CONSTRAINT Editors_C
PRIMARY KEY (EditorID)
);

Is there some other business rule about Name like it must also be
unique? If so, use this one:

CREATE TABLE Editors
(
EditorID INTEGER NOT NULL,
Name VARCHAR(30) NOT NULL,
Comment BLOB,
CONSTRAINT Editors_C
PRIMARY KEY (EditorID),
CONSTRAINT UQ_Editors_1 UNIQUE (Name)
);

If your editors table looks like that then a single record in the
characters table is only pointing to a single editors record (which is
I think what you need). If you needed to assign multiple editors to
the single character, then you need to create a new table called
EditorsCharacter, and create an EditorID and a CharacterID field in
this 'link' table.

Adam