Dear ZioNemo and others,

I think Helen has already made the right point. Should you ever get
the same error message again, but then in a case where you are trying
to create a primary or foreign key and one of the referenced fields
is not yet displayed as "NOT NULL", you can try a hack similar to the
one described in the following. I had a field FVIEWID in a table
which was only declared with a default:
CREATE TABLE "TMENUITEMS" (
"FCODE" VARCHAR(12) NOT NULL,
"FVIEWID" INTEGER DEFAULT 0,
"FNAME" VARCHAR(40) NOT NULL,
"FPATH" VARCHAR(80) NOT NULL,
etc.

I checked the tables rdb$relation_constraints and rdb$relation$fields
and I also made a backup of my database file. Then I did this:
INSERT INTO rdb$relation_constraints
VALUES ('INTEG_62', 'NOT NULL', 'TMENUITEMS', 'NO', 'NO', NULL);
COMMIT;
UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG=1
WHERE RDB$RELATION_NAME='TMENUITEMS' AND rdb$field_name='FVIEWID';
COMMIT;

This worked for me. Alternatively you can also get Database
Workbench ...

Kind regards,
Dobedani

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> 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
>