Employee/History - Part of composite key as foreign key

Question

I've got 2 entities:

1) EMPLOYEES (Parent)

CREATE TABLE EMPLOYEES (
employee_id       NUMBER   (3)    NOT NULL,
first_name        VARCHAR (20)    NOT NULL,
last_name         VARCHAR (20)    NOT NULL,
job_title         VARCHAR (20)    NOT NULL,
employee_type     VARCHAR  (1)    NOT NULL,
salary            NUMBER   (5),
hourly_pay        NUMBER (5,2),
bonus_pay         NUMBER (5,2),

CONSTRAINT employee_pk PRIMARY KEY(employee_id));

2) EMPLOYEE_HISTORY (Child)

CREATE TABLE EMPLOYEE_HISTORY (
start_date        DATE            NOT NULL,
employee_id       NUMBER   (3)    NOT NULL,
end_date          DATE,
job_title         VARCHAR (10)    NOT NULL,
hourly_rate       NUMBER (5,2)    NOT NULL,

CONSTRAINT employee_history_pk PRIMARY KEY(start_date, employee_id));

I'm trying to create:

ALTER TABLE employee_history
  ADD CONSTRAINT employee_history_fk 
    FOREIGN KEY (employee_id)
      REFERENCES employee_history(employee_id);

When I do this, I get an error

ORA-02270: no matching unique or primary key for this column-list

My guess is that I cannot create the constraint on just employee_id because I have a composite key in my child table. I understand when an employee gets put into the database, the parent table is filled out and the "start date" should be filled out along with everything else. However, I do not understand how this would work if I had start_date in my parent table as well. I would be able to create my constraint, yes, but how will I be able to keep a record of changes in start_date if my start_date was inputted at the time of when the employee was entered into the database.

I thought about using job_title as a primary key instead of start_date because it's present in both tables, but what happens when an employee gets promoted and demoted again? Won't a duplicate value constraint come up when the same employee_id and job_title is getting inserted?

Answer 1

Your references clause needs to reference the parent table. Not the child table

ALTER TABLE employee_history
  ADD CONSTRAINT employee_history_fk 
    FOREIGN KEY (employee_id)
      REFERENCES employee(employee_id); -- employee not employee_history

The SQL you posted is trying to create a self-referential foreign key where employee_history is both the parent and the child. That doesn't make sense in this case.