> >ISC ERROR MESSAGE:
> >unsuccessful metadata update
> >object VWMONITORS is in use
>
> OK...*that* particular message will occur when you try to add
> a foreign key constraint to *any* table and you don't have
> single-transaction, exclusive access to the database.

I only get this message will using Database WorkBench. However if I run the
script using ISQL then it works perfectly. On the other hand if I run the
script using the IBOjects TIB_Script (at design time) I get a tottally
different error:

Column: cw_vw_id not define as not null - anot be used in PRIMARY
KEY/UNIQUE constraint definition

> >ALTER TABLE CWMONITORS ADD CONSTRAINT U_CWMONITORS UNIQUE
> >(CW_VW_ID);
>
> Yes, you can have a unique constraint on this nullable
> column. However,
> even though the column is defined as nullable, once you start
> using the
> table, you will be able to have one and only one record in
> the entire table
> that has NULL in this column.

Unfortunetly this is not true, please run the following scripts and be
surprised :-)


INSERT INTO VWMONITORS(VW_ID) VALUES (1);
INSERT INTO VWMONITORS(VW_ID) VALUES (2);
INSERT INTO VWMONITORS(VW_ID) VALUES (3);
INSERT INTO VWMONITORS(VW_ID) VALUES (4);
INSERT INTO VWMONITORS(VW_ID) VALUES (6);
INSERT INTO VWMONITORS(VW_ID) VALUES (7);
INSERT INTO VWMONITORS(VW_ID) VALUES (8);
INSERT INTO VWMONITORS(VW_ID) VALUES (9);


INSERT INTO CWMONITORS(CW_ID, CW_VW_ID) VALUES (1, 1);
INSERT INTO CWMONITORS(CW_ID, CW_VW_ID) VALUES (2, 2);
INSERT INTO CWMONITORS(CW_ID, CW_VW_ID) VALUES (3, NULL);
INSERT INTO CWMONITORS(CW_ID, CW_VW_ID) VALUES (4, NULL);
INSERT INTO CWMONITORS(CW_ID, CW_VW_ID) VALUES (5, NULL);
INSERT INTO CWMONITORS(CW_ID, CW_VW_ID) VALUES (6, 3);
INSERT INTO CWMONITORS(CW_ID, CW_VW_ID) VALUES (7, 4);

> >ALTER TABLE CWMONITORS ADD CONSTRAINT FK_CWMONITORS
> > FOREIGN KEY (CW_VW_ID) REFERENCES VWMONITORS
> > (VW_ID)
> > ON DELETE CASCADE
> > ON UPDATE CASCADE;
>
> This constraint ought to fail, because it is nullable. Allowing the
> constraint, with the CASCADE action rules will cause a orphan
> if any record
> were posted to CWMONITORS that had null in the foreign key
> column, because
> there would be no way for the cascade actions to be applied
> to such a record.

Well I may be totally breaking the rules here, but what I would like, is to
have a table which has a foreigh key, and allow for that key to be null.
However, if the key is not null, I will like the Constraint to remove the
record in the child table, when the record in the parent table is removed.
Thus the ON DELETE CASCADE. Please fell free to tell me off.

> I say *ought to fail*, because I ran your script (with
> commits) and it
> completed without exception.
>
> The other possible problem here is that your UNIQUE
> constraint enforces a
> 1:1 relationship between CWMONITORS and the "parent". If this was
> intentional, then that's cool. If not, then remove the
> UNIQUE constraint
> and be aware that you have the possibility of an orphan in CWMONITORS.
>
> Your 1:1 relationship and the cascade action rules are broken by a
> statement like this:
>
> insert in vwmonitors values(1);
> commit;
> insert into cwmonitors values (1,null);

Well this is exactly what I would like, i.e. the posability for a record to
contain a reference or not to contain a reference.

>
> The nullable character of the domain, the "one-null" rule of
> the unique
> constraint and the rules for foreign key indexes allow this
> orphan row to
> happen. It think it needs to be reported as a bug (a
> possible side-effect
> from the changes to the uniqueness rules in 1.5). Until or
> unless it is
> corrected, beware of it - don't allow nulls on foreign key
> columns unless
> you've a reason to want them there, i.e. you plan to implement the ON
> DELETE | UPDATE SET NULL action rule.

Will you report this as a bug, or should I, as I am not sure I can consider
this a bug.

Thanks

Daniel

____________________________
Comvision Pty. Ltd.

www.comvision.net.au