Re: constrain with MATCH full and NULL values in referenced table

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(dot)klaver(at)aklaver(dot)com