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_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