Helen Borrie wrote:

> At 10:49 PM 28/03/2009, you wrote:
>> Helen Borrie wrote:
>>> At 10:13 PM 28/03/2009, Mark Rotteveel wrote:
>>>>> It's wrong according to the LangRef, though.
>>>> Actually according to the LangRef it is allowed (see the syntax on page
>>>> 72, and the notes on page 77-78).
>>> I don't think you looked closely enough at his table definition. ;-)
>> Could you enlighten me,
>
> CREATE TABLE tblFiles (
> ID INTEGER
> NOT NULL
> PRIMARY KEY
> CHECK (VALUE >= 0),
> Filename CHAR (260) CHARACTER SET UTF8
> NOT NULL,
> Volume INTEGER
> NOT NULL
> DEFAULT -1
> CHECK (VALUE >= -1)
> REFERENCES tblVolumes (ID)
> ON UPDATE SET DEFAULT
> ON DELETE SET DEFAULT,
>
> UNIQUE (Volume, Filename)
> );
>
> No sign of the FOREIGN KEY token.

That is because 'FOREIGN KEY' is not allowed if it is an inline
declaration of the foreign key. It is however required if you use an
out-of-line foreign key definition
From page 72 of the LangRef:
<col_def> = col {<datatype> | COMPUTED [BY] (<expr>) | domain}
[DEFAULT {literal | NULL | USER}]
[NOT NULL]
[<col_constraint>]
[COLLATE collation]

and

<col_constraint> = [CONSTRAINT constraint]
{ UNIQUE
| PRIMARY KEY
| REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK (<search_condition>)}

So this is valid:
CREATE TABLE (
ID INTEGER PRIMARY KEY
LALA_ID INTEGER REFERENCES LALA(ID)
);

Is perfectly valid and conforms to the SQL specs.

>> But still, that does match with your assertion that foreign keys must be
>> declared explicitly (they don't)
>
> They do.
>
> "Explicitly" doesn't mean "in a separate statement" (although it *is* advisable).
> But you can't have an "implicit foreign key" created by just tacking

a REFERENCES

> clause onto a column definition.

Yes you can, and I have been done that occasionally ever since I was
introduced to Firebird 1.0.

>> and that it is advisable to declare
>> them in a separate transaction (why? I see no need for that, and if
>> there is some hidden reason that should be considered a bug).

> Anything that is dependent on something else should be in its own transaction.
> Apart from being clean practice, it also ensures that each dependency

and things

> dependent on those dependencies are taken care of in good order.

That doesn't make sense, and I even believe that being able to define
foreign keys inside a CREATE TABLE is a requirement of SQL. The only
reason there can be to define a foreign key in a separate alter table is
if you otherwise can get circular or definition order problems.

> Why? because DDL *looks* simple but it involves a lot of stuff under the covers:
> creation of objects, indices, checking and creation of dependencies,

yada, yada.

> In any case, Fb 2 and beyond are a lot tougher on lazy typists than 1.5 was. ;-)
> You will suffer much frustration by carrying your bad old habits

forward...and I

> certainly don't think it's helpful to encourage newbies to do sloppy

stuff that

> will get them into strife when they upgrade.

If Firebird cannot handle this correctly in all relevant cases (although
I have never seen any!), that is really a problem.

--
Mark Rotteveel