On 8/12/19 10:06 AM, stan wrote:
Cc: pgsql-general.lists.postgresql.org@xxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxxxx
