Hi,

> Here is the test case:
>
> CREATE DOMAIN TID AS INTEGER NOT NULL;
> CREATE TABLE ENTITY (ENTITYID TID, ALLOTHERFIELDS TID,
> CONSTRAINT PK_ENTITY PRIMARY KEY (ENTITYID));
>
> SELECT RDB$NULL_FLAG FROM RDB$RELATION_FIELDS
> WHERE RDB$RELATION_NAME = 'ENTITY' AND RDB$FIELD_NAME = 'ENTITYID'
>
> Returns 'NULL' if executed on FB 1.5.3 server and
> Returns '1' if executed on FB 2.0.1 server.
>
> Why? Was that done intentionally?

I'm not sure why (and "if"), but if the domain is created as NOT NULL, the
flag in RDB$FIELDS should say so. A nullable domain can be overridden in
RDB$RELATION_FIELDS by setting the NULL_FLAG to 1. If the domain
is not nullable, the RELATION_FIELDS table will not be able to override it
(to "not null"), and it used to be null/0. But they one could think it was
overridden
to become nullable again.

I can see it's reasoning, but I can also see yours.

Database Workbench seems to check to see if the domain is not nullable and
will extract the DDL for the table without the NOT NULL part.

> It may seem like a not-so-important detail, however,
> if you extract and compare metadata of the two, you will find
> differences:
>
> CREATE TABLE ENTITY (
> <! ENTITYID TID NOT NULL,
> !> ENTITYID TID,
>
> which are not real differences at all, and that makes metadata
> comparison more complicated than it could be.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com