CONSTRAINT_HR_MODEL.sql

-- CONSTRAINT | KISITLAMA OLUÞTURMA
-- PRIMARY KEY | TEKÝL ANAKHAR
-- FOREIGN KEY | YABANCIL ANAHTAR
-- HR MODELÝMÝZDEKÝ TABLOLARA KEY KISITLAMALARI OLUÞTURMA.

ALTER TABLE REGIONS ADD(
      CONSTRAINT reg_id_pk PRIMARY KEY (REGION_ID)
);

ALTER TABLE COUNTRIES ADD(
      CONSTRAINT countr_reg_fk FOREIGN KEY (REGION_ID) REFERENCES REGIONS (REGION_ID),
      CONSTRAINT countr_id_pk PRIMARY KEY (COUNTRY_ID)    
);

ALTER TABLE LOCATIONS ADD(
      CONSTRAINT loc_id_pk PRIMARY KEY (LOCATION_ID),
      CONSTRAINT loc_c_id_fk FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRIES (COUNTRY_ID)
);

ALTER TABLE DEPARTMENTS ADD(
      CONSTRAINT dep_id_pk PRIMARY KEY (DEPARTMENT_ID),
      CONSTRAINT dep_loc_fk FOREIGN KEY (LOCATION_ID) REFERENCES LOCATIONS (LOCATION_ID)
);

ALTER TABLE JOBS ADD(
      CONSTRAINT job_id_pk PRIMARY KEY (JOB_ID)
);

ALTER TABLE EMPLOYEES ADD(
      CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID),
      CONSTRAINT emp_dept_fk FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS (DEPARTMENT_ID),
      CONSTRAINT emp_job_fk FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID),
      CONSTRAINT emp_meneger_fk FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID)
);

ALTER TABLE DEPARTMENTS ADD(
      CONSTRAINT dept_mgr_id FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID)
);

ALTER TABLE JOB_HISTORY ADD (
      CONSTRAINT jhist_emp_id_date_pk PRIMARY KEY (EMPLOYEE_ID, START_DATE),
      CONSTRAINT jhist_job_id_fk FOREIGN KEY (JOB_ID) REFERENCES JOBS (JOB_ID),
      CONSTRAINT jhist_emp_id_fk FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID),
      CONSTRAINT jhist_dept_fk FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS (DEPARTMENT_ID)
);