Stan:
On Mon, Aug 12, 2019 at 5:11 PM stan <stanb@panix.com> wrote:
>
> I am creating a table that has 2 values in it which are keys pointing to 2
> other tables. I need for the UNIQUE combination of these 2 keys to exist in
> a fourth table. It has been recommended to use a foreign key constraint with
> the MATCH FULL parameter.
>
> Here is my question, does this deal with NULLS in the 4th table? I am
> concerned that this constraint might fail to reject an entry if one, or both
> of the 2 key values being inserted in the table are NULLS,.
If you have:
Table TA (a: PK)
Table TB (b: PK)
Table TAB( a, b,....) PK(A,B), FK(a ref TA), FK(b ref TB)
Table FOURTH(a,b,...) FK((A,B) ref TAB mach full)
Note TAB cannot have nulls in A,B as it is the PK.
And you insert (null, null) in FOURTH it will be treated as in single
column, allowed by the fk ( but you may have non null constraints on
either a or b).
If you try to insert (a1, null) or (null, b1), it will ber rejected,
MATCH FULL does not allow null/non-null mix.
OTOH, if you use MATCH SIMPLE the partial-null cases will be not
checked at all, as if they where not null. As stated in the docs, you
can use extra single column FK in a and/or b to get them checked in
TA/TB, and also you can put non-null constraints on either on them.
The exact combo depends on what you are trying to model, which gives
you what you want. I.e., say I want to:
1.- check a,b combos.
2.- Allow (a,null) but have it checked against ta.
3.- Forbid (null,b)
4.- Aloow (null, null)
You can use MATCH simple FK(a,b) against TAB for (1,4), single column
FK(a) against TA for(2) and a check constraint (A is not null OR B is
null , If I'm not confused ) for (3,4).
( Note you do not have to check b against tb, because if b is present,
a is present, a,b is checked against TAB and TAB.b is checked against
TB ).
(match simple checks 1 and allows 2,3,4, FK(a) checks 2, and the check
constraint forbids 3)
The DB deals with nulls in many way, you just have to enumerate your
conditions and elaborate on that.
Note in this case it FAILS to reject an entry if b is null, because I
dessigned it that way, but DOES REJECT if a is null and B is not.
Regards.
Francisco Olarte.