Re: Subject: Re: constrain with MATCH full and NULL values in referenced table
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_keyinteger 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 ,
> > > > descripvarchar UNIQUE ,
> > > > modtimetimestamptz DEFAULT current_timestamp
> > > > );
> > > >
> > > > CREATE TABLE rate (
> > > > employee_key integer NOT NULL,
> > > > work_type_key integer NOT NULL,
> > > > rate numeric (5, 2) NOT NULL,
> > > > descripvarchar ,
> > > > modtimetimestamptz 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_keyinteger NOT NULL ,
> > > > employee_key integer NOT NULL ,
> > > > work_type_key integer NOT NULL ,
> > > > hoursnumeric (5, 2) NOT NULL ,
> > > > work_start timestamptz ,
> > > > work_end timestamptz ,
> > > > modtimetimestamptz DEFAULT current_timestamp ,
> > > > descripvarchar ,
> > >
> > > 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
Re: Subject: Re: constrain with MATCH full and NULL values in referenced table
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_keyinteger 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 ,
descripvarchar UNIQUE ,
modtimetimestamptz DEFAULT current_timestamp
);
CREATE TABLE rate (
employee_key integer NOT NULL,
work_type_key integer NOT NULL,
rate numeric (5, 2) NOT NULL,
descripvarchar ,
modtimetimestamptz 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_keyinteger NOT NULL ,
employee_key integer NOT NULL ,
work_type_key integer NOT NULL ,
hoursnumeric (5, 2) NOT NULL ,
work_start timestamptz ,
work_end timestamptz ,
modtimetimestamptz DEFAULT current_timestamp ,
descripvarchar ,
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.kla...@aklaver.com
Re: Subject: Re: constrain with MATCH full and NULL values in referenced table
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@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 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_keyinteger 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 ,
> > descripvarchar UNIQUE ,
> > modtimetimestamptz DEFAULT current_timestamp
> > );
> >
> > CREATE TABLE rate (
> > employee_key integer NOT NULL,
> > work_type_key integer NOT NULL,
> > rate numeric (5, 2) NOT NULL,
> > descripvarchar ,
> > modtimetimestamptz 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_keyinteger 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 ,
> > modtimetimestamptz DEFAULT current_timestamp ,
> > descripvarchar ,
>
> Aren't the marked ones below redundant?:
>
> > FOREIGN KEY (employee_key) references employee(employee_key) ,
>^^
> > FOREIGN KEY
Re: Subject: Re: constrain with MATCH full and NULL values in referenced table
On 8/12/19 10:06 AM, stan wrote:
Cc: pgsql-general.lists.postgresql@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 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_keyinteger 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 ,
descripvarchar UNIQUE ,
modtimetimestamptz DEFAULT current_timestamp
);
CREATE TABLE rate (
employee_key integer NOT NULL,
work_type_key integer NOT NULL,
rate numeric (5, 2) NOT NULL,
descripvarchar ,
modtimetimestamptz 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_keyinteger 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 ,
modtimetimestamptz DEFAULT current_timestamp ,
descripvarchar ,
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.kla...@aklaver.com
Subject: Re: constrain with MATCH full and NULL values in referenced table
Cc: pgsql-general.lists.postgresql@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 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_keyinteger 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 ,
descripvarchar UNIQUE ,
modtimetimestamptz DEFAULT current_timestamp
);
CREATE TABLE rate (
employee_key integer NOT NULL,
work_type_key integer NOT NULL,
rate numeric (5, 2) NOT NULL,
descripvarchar ,
modtimetimestamptz 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_keyinteger 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 ,
modtimetimestamptz DEFAULT current_timestamp ,
descripvarchar ,
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
