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