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_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(dot)klaver(at)aklaver(dot)com