|
Hi ive got a set of tables where there is table of Employees and one of Projects and a third one of Allocations which refrences the Employee ID and Project ID and uses the pair as the primary key then it also has an Hourly Rate in allocation table. Now that all works fine but I need to create a 4th table Time Log which uses Employee ID, Project ID and Week number from the table as the primary key as well as other information. Im having trouble getting it to reference the allocate table. If I refrence to Employee and Project tables then it works but then employee and project pairs that dont exist in the allocations table can be entered in time log which shouldnt be able to happen (however i understand why it does from the constraints) is there a way to get it to only accept pairs of Employees amd Projects that only exist in the allocation table? I tried just referencing the allocation table but I get the error "ORA-02256: number of referencing columns must match referenced columns" Is there something that I am doing wrong? is there a way to reference the pair? my create table statement for time Log as it is now is below, which dosent work due to the reference not working CREATE TABLE timeLog ( EmpID number(3) references allocation, ProjID varchar2(2) references allocation, WeekNo number not null, HrsWorked number not null, PRIMARY KEY (EmpID, ProjID, WeekNo)); |
