Some will remember my long post of some days ago stating my disappointment
in IB.

Well, I have found that some of our problems are (were) due to us using a
specific IB tool. Before I name the tool, I have to say that it is one of
the most powerful IB tools I have used. It is a very productive way of
managing an IB DB. It is clear that the tool developers are also IB
developers. I am very happy with it, but it is a loaded gun as it
manipulates system tables and actually compromise the integrity of the DB.

Try the following with IB Expert V1.0.2.92:

1. Create a new table T1 with a first column called ID as an INTEGER and
checking the 'NOT NULL' check box. The DDL it executes is correct.
2. Create a PK constraint on the ID column by using the constraints tab and
adding the prim. key
3. Insert a record with ID = 1 using the data tab and editing inside the
grid. Commit the changes

So far so good

4. Create a new table T2 with a first column called ID as an INTEGER and
checking the 'NOT NULL' check box.
5. Create a PK constraint on the ID column by using the constraints tab and
adding the prim. key
6. Add a nullable column FK of type INTEGER to table T2 using the fields tab
7. insert a row (ID,FK) values (1,null) and commit changes
8. Add a foreign key on column FK table T2 to reference table T1.ID. The
foreign key is created no problem as the column is nullable.

So far so good

9. Use the fields tab and double - click on the FK column to edit it. Check
the NOT NULL check box. If you look at the DDL it executes, you see the
following:

update rdb$relation_fields set rdb$null_flag = 1 where (rdb$field_name =
'FK') and (rdb$relation_name = 'T2')

The result of this action is a FK column value of 0 in table T2, with a
foreign key referencing table T1 ID and WITH NO ID COLUMN VALUE OF 0....!!!!!!

In my opinion, this is illegal...!!!

I think the problem is with the DDL that is executed as part of the NOT
NULL making excercise. I don't know much about IB internals, but what it
seems like is that the trigger that fires on a column add does not fire to
ensure ref. integrity due to the process being short-circuited by IB
Expert. As far as I know, you should drop the FK column and re-add it with
a NOT NULL specifier and then add the FKey.

This is one example of dangerous things that IB Expert does / allows. There
are a couple of other examples like this.

It also allowed me to change a domain definition from VARCHAR(60) to DATE
while I had a column using the domain with populated values!!!
Here is the DDL:

update RDB$FIELDS set
RDB$FIELD_TYPE = 35,
RDB$FIELD_LENGTH = 8,
RDB$CHARACTER_LENGTH = NULL,
RDB$CHARACTER_SET_ID = NULL,
RDB$COLLATION_ID = NULL
where RDB$FIELD_NAME = 'EMAIL'

AFAIK this is also pretty dangerous. Scary stuff....

It's very nice to be able to make changes not normally allowed, but it
screws your database in the process. Our policy is now..: ALL METADATA
CHANGES DONE BY SCRIPT ONLY.

Regards




L.J. van Alphen
Director
Basix Automation

E-mail: lja@...
WWW: http://www.basix.co.za
Tel: (+27) (0)21-409 7018


P.O. Box 6330
Roggebaai
8012
Cape Town
South Africa