| Subject | Re: [firebird-support] Re: Alternative for UNIQUE constraint |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-03-28T12:47:13Z |
At 10:49 PM 28/03/2009, you wrote:
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.
"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.
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.
./hb
>Helen Borrie wrote:CREATE TABLE tblFiles (
>> 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,
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.
>the only things I see wrong with it are:They do.
>* 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)
"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.
>and that it is advisable to declareAnything 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.
>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).
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.
./hb