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@xxxxxxxxxxx
