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

Cc: pgsql-general(dot)lists(dot)postgresql(dot)org(at)panix(dot)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(at)panix(dot)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 ,
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 that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

----- End forwarded message -----

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