>> Let's create two unites from one lot:
>>
>> INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A1', 'L1', 'S1',
>> 'abc'); INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A2',
>> 'L1', 'S1', 'bcd');
>>
>> I expected that the following instruction will not be exectued:
>> INSERT INTO LOCATION (ID, UCODE, ULOT, USITE, ZONE, WAREHOUSE)
>> VALUES (1, 'A3', NULL, NULL, NULL, NULL); because there is no A3 entry in
>> unites table, but it IS! Firebird allows to add record with value which doesn't
>> exist in master table.

> I believe that your schema for the LOCATION table is incorrect.

> UCODE, ULOT and USITE should be described as NOT NULL, since a
> Location must relate to a UNITES (which has CODE, LOT and SITE described as NOT NULL), no?

As Karol and Sean have noted.

Also, are you sure you have this relationship facing in the right
direction? Do locations depend on units? or do units depend on
locations?

Normally, a Location would be a primary entity and Unit a secondary
one. So a foreign key relationship wants to ensure that a Unit is not
booked in having a Location that does not exist, including any key
elements that are NULL.

Obviously, language differences are in play here, so your
conceptualization of the relationship might be correct for your case.
But if the dependency is as I described it, then the FK should be in
Unites, referencing the PK in Location. And - simply - never allow
NULL to be written to a constraint element.

AFAIR, Firebird follows some standard that allows one record with one
NULL element, so with your 3-element key, you have the potential to
have three useless reference records. It's one of those cases where
"just because you can does not mean you should".

Helen