Обсуждение: constrain with MATCH full and NULL values in referenced table

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



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 8/12/19 8:11 AM, stan 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.

Without the table schema it difficult for me to figure out what it is 
you are attempting.

See below for how MATCH FULL works:

https://www.postgresql.org/docs/11/sql-createtable.html

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com

stan <stanb@panix.com> writes:
> 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 don't want nulls there, maybe add NOT NULL constraints to those
columns?

            regards, tom lane

On 8/12/19 8:51 AM, stan wrote:
Please reply to list also.
Ccing list.

> On Mon, Aug 12, 2019 at 08:17:33AM -0700, Adrian Klaver wrote:
>> On 8/12/19 8:11 AM, stan 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.
>>
>> Without the table schema it difficult for me to figure out what it is you
>> are attempting.
>>
>> See below for how MATCH FULL works:
>>
>> https://www.postgresql.org/docs/11/sql-createtable.html
>>
>  From that page:
> 
> MATCH FULL will not allow one column of a multicolumn foreign key to be null
> unless all foreign key columns are null; if they are all null, the row is
> not required to have a match in the referenced table.
> 
> I think that means that what I am trying to enforce will not work.
> 
> The table the insert is on has 2 columns, each of these is a foreign key to
> other table. I need the unique combination of these 2 keys to exist in a 3rd
> table that is a rate table. It has 3 columns, key 1, key 2, and rate. Looks
> like to me, if neither of the 2 keys are in the rate table the constraint
> will allow the insert. Do I have this wrong?

The docs are referring to a multicolumn FK so something like:

create table parent_tbl(fld_1 integer, fld_2 integer, UNIQUE(fld_1, fld_2));

create table child_tbl(fk_fld_1 integer, fk_fld_2 integer, FOREIGN KEY 
(fk_fld_1, fk_fld_2) REFERENCES parent_tbl(fld_1, fld_2));

\d child_tbl
               Table "public.child_tbl"
   Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
  fk_fld_1 | integer |           |          |
  fk_fld_2 | integer |           |          |
Foreign-key constraints:
     "child_tbl_fk_fld_1_fkey" FOREIGN KEY (fk_fld_1, fk_fld_2) 
REFERENCES parent_tbl(fld_1, fld_2)

Not sure what your setup is. That is why it is important to show the 
actual schema.



> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.