At 02:35 PM 28/10/2004 +1000, you wrote:

>ALTER TABLE CWMONITORS ADD CONSTRAINT FK_CWMONITORS
> FOREIGN KEY (CW_VW_ID) REFERENCES VWMONITORS
> (VW_ID)
> ON DELETE CASCADE
> ON UPDATE CASCADE;
>
>
>Before I forget, the error is:
>
>ISC ERROR CODE:335544351
>
>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.

However, given the absence of either COMMIT statements or a SET AUTODDL ON
statement in this script, you will also get this exception thrown because
you are trying to add the FK and UNIQUE constraints to uncommitted tables.

But implementing the DDL in this script is going to give you problems,
too. Let's pace it out:

>I would really appreciate if you could point out the possible errors in
>this script:
>
>
>CREATE DOMAIN D_NON_NULL AS
> SMALLINT
> NOT NULL
> CHECK (VALUE IS NOT NULL AND VALUE > 0);

Any columns created under this domain will be non-nullable.

>CREATE DOMAIN D_NULL AS
> SMALLINT
> DEFAULT NULL
> CHECK (VALUE IS NULL OR VALUE > 0);

Any columns you create under this domain will be nullable unless you add
additional constraints at table-level.

>CREATE TABLE CWMONITORS
>(
> CW_ID D_NON_NULL,
> CW_VW_ID D_NULL ,
> CONSTRAINT PK_CWMONITORS PRIMARY KEY (CW_ID)
>);

This would work. A PRIMARY KEY constraint can only be applied to a
non-nullable column.

>CREATE TABLE VWMONITORS
>(
> VW_ID D_NON_NULL ,
> CONSTRAINT PK_VWMONITORS PRIMARY KEY (VW_ID)
>);

As above.

>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.

>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.

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);

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.

./heLen