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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

stan-9
Cc: [hidden email]
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
On 8/12/19 10:06 AM, stan wrote:

> Cc: [hidden email]
> 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 <[hidden email]> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

stan-9
On Mon, Aug 12, 2019 at 10:16:41AM -0700, Adrian Klaver wrote:

> On 8/12/19 10:06 AM, stan wrote:
> > Cc: [hidden email]
> > 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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

stan-9
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