Re: Subject: Re: constrain with MATCH full and NULL values inreferenced 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