Thread: Subject: Re: constrain with MATCH full and NULL values in referencedtable
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 , 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
Re: Subject: Re: constrain with MATCH full and NULL values inreferenced table
From
Adrian Klaver
Date:
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
On Mon, Aug 12, 2019 at 10:16:41AM -0700, Adrian Klaver wrote: > 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. > > > ); OK, looks like learning time for me, again. Which is alwasy a good thing. My thought here was that I needed to specify these on the dreivative table (task_instnce). Are you teaching me that, since these constraints exist on the tables that rate is derived from, I do not need to specify thmm for the rate table? The purpose of those is to verify that the key being inserted already exists in the parent (eg employee) table. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Subject: Re: constrain with MATCH full and NULL values inreferenced table
From
Adrian Klaver
Date:
On 8/12/19 10:30 AM, stan wrote: >>> 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. >> >>> ); > > OK, looks like learning time for me, again. Which is alwasy a good thing. My > thought here was that I needed to specify these on the dreivative table > (task_instnce). Are you teaching me that, since these constraints exist on > the tables that rate is derived from, I do not need to specify thmm for the > rate table? If I'm following what you are trying to do then: 1) task_instance is dependent on the information in rate being present for a given combination of (work_type_key , employee_key). 2) If 1) is correct then you cannot create a record in task_instance until a record exists in rate. 3) 2) means you have already established a relationship to employee and work_type via rate. > > The purpose of those is to verify that the key being inserted already exists > in the parent (eg employee) table. > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Aug 12, 2019 at 10:40:20AM -0700, Adrian Klaver wrote: > On 8/12/19 10:30 AM, stan wrote: > > > > 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. > > > > > > > ); > > > > OK, looks like learning time for me, again. Which is alwasy a good thing. My > > thought here was that I needed to specify these on the dreivative table > > (task_instnce). Are you teaching me that, since these constraints exist on > > the tables that rate is derived from, I do not need to specify thmm for the > > rate table? > > If I'm following what you are trying to do then: > > 1) task_instance is dependent on the information in rate being present for a > given combination of (work_type_key , employee_key). > > 2) If 1) is correct then you cannot create a record in task_instance until a > record exists in rate. > > 3) 2) means you have already established a relationship to employee and > work_type via rate. > Ah subtle. Makes sense. In case it is not glaringly obvious to the casual observer, i am just returning to the database world, after having spent many years in a totaly unrelated on (power systens for large indutrials, if you are curios). -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin