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