--- In firebird-support@yahoogroups.com, Mark Rotteveel
<Avalanche1979@...> 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, the only things I see wrong with it are:
> * declaration of NOT NULL before DEFAULT (not allowed)
> * an inline CHECK constraint and inline FOREIGN KEY constraint (not

allowed)

> * the CHECK constraint is incorrect (should be CHECK (id >= -0) resp
> CHECK (volume >= -1)).
>
> But still, that does match with your assertion that foreign keys must

be

> declared explicitly (they don't) 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).
>
> Mark
> --
> Mark Rotteveel
>

The reason for those errors was that I copied and pasted some lines from
some CREATE DOMAIN statements into the CREATE TABLE example I put in my
first post. I wanted to show what I was trying to do in as little lines
as possible and in my haste I forgot to test it or check if it was valid
SQL.

The relevant lines of code in my app (which worked after recreating the
DB with the higher page size) was:
SET TERM .. ; CREATE DOMAIN VolumeType AS SMALLINT DEFAULT 0 NOT
NULL CHECK (VALUE BETWEEN 0 AND 4).. CREATE DOMAIN ID AS INTEGER
NOT NULL CHECK (VALUE >= 0).. CREATE DOMAIN IDREF AS INTEGER
DEFAULT -1 NOT NULL CHECK (VALUE >= -1).. CREATE DOMAIN FilePath AS
CHAR (260) CHARACTER SET UTF8.. /* ... more CREATE DOMAIN statements */
CREATE TABLE tblVolumes ( ID ID PRIMARY KEY, Type
VolumeType, Path FilePath, ParentVolume IDREF
REFERENCES tblVolumes (ID) ON UPDATE SET DEFAULT ON DELETE
SET DEFAULT, DiskNumber NonNegativeInteger, Description
DescriptionText ).. CREATE TABLE tblFiles ( ID ID
PRIMARY KEY, Filename FilePath NOT NULL, VolumeID
IDREF REFERENCES tblVolumes (ID) ON UPDATE SET DEFAULT
ON DELETE SET DEFAULT, UNIQUE (VolumeID, Filename) ).. /* *
... more CREATE TABLE statements, then some CREATE GENERATOR statements
* and CREATE TRIGGER statements for auto-incrementing the IDs. * */
SET TERM ; ..


[Non-text portions of this message have been removed]