Re: Subject: Re: constrain with MATCH full and NULL values inreferenced table

On 8/12/19 10:06 AM, stan wrote:
> Cc: pgsql-general.lists.postgresql.org@panix.com
> Subject: Re: constrain with MATCH full and NULL values in referenced table
> User-Agent: Mutt/1.12.1 (2019-06-15)
> X-Editor: gVim
> 
> On Mon, Aug 12, 2019 at 06:22:54PM +0200, Francisco Olarte wrote:
>> 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.
>>
> 
> Thank you.
> 
> Testing seems to verify that I have this correct.
> 
> I thought I would include what I came up with, so it gets in the archive.
> Some fields eliminated for clarity.
> 
> The task_instance table is the one the original question was in reference
> to.
> 
> CREATE TABLE employee (
>      employee_key            integer DEFAULT nextval('employee_key_serial')
>      PRIMARY KEY ,
>      id                     varchar(5)  NOT NULL UNIQUE ,
>      first_name             varchar  NOT NULL,
> );
> 
> CREATE TABLE work_type (
>      work_type_key      integer DEFAULT nextval('work_type_key_serial')
>      PRIMARY KEY ,
>      type               smallint UNIQUE ,
>      descrip            varchar UNIQUE ,
>      modtime            timestamptz DEFAULT current_timestamp
> );
> 
> CREATE TABLE rate (
>      employee_key       integer NOT NULL,
>      work_type_key      integer NOT NULL,
>      rate            numeric (5, 2) NOT NULL,
>      descrip            varchar ,
>      modtime            timestamptz DEFAULT current_timestamp ,
>      FOREIGN KEY (employee_key) references employee(employee_key) ,
>      FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
>      CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
> );
> 
> 
> CREATE TABLE task_instance (
>      task_instance      integer DEFAULT nextval('task_instance_key_serial')
>      PRIMARY KEY ,
>      project_key        integer NOT NULL ,
>      employee_key       integer NOT NULL ,
>      work_type_key      integer NOT NULL ,
>      hours            numeric (5, 2) NOT NULL ,
>      work_start         timestamptz ,
>      work_end           timestamptz ,
>      modtime            timestamptz DEFAULT current_timestamp ,
>      descrip            varchar ,

Aren't the marked ones below redundant?:

>      FOREIGN KEY (employee_key) references employee(employee_key) ,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>      FOREIGN KEY (project_key) references project(project_key) ,
>      FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>      FOREIGN KEY (work_type_key , employee_key) REFERENCES rate (work_type_key , employee_key) MATCH FULL

They are covered above.

> );


> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com