Re: Re: [firebird-support] Multi-column Foreign key

[Paweł Świerzko p_swier...@poczta.onet.pl [firebird-support]]

Dear All
  thank you for your quick response and support.
  The example I have attached was just for help to illustrate problem and is 
not so important.
  I expected firebird to create in such situation the following check in the 
backgroud:
 
   ALTER TABLE LOCATION ADD CONSTRAINT CHK1_LOCATION CHECK
  ((ucode is null) and (ulot is null) and (usite is null))
    or
  ((ucode is not null) and (ulot is not null) and (usite is not null))
 
  So if it is not done by engine and it is not a bug - I will add it when it 
will be required.
Thanks again
Pawel
  
 
  
 
W dniu 2017-08-10 00:09:53 użytkownik Helen Borrie hele...@iinet.net.au 
[firebird-support] <firebird-support@yahoogroups.com> napisał:
 
>> 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