HR.JOB HISTORY (table)

wikibot

Table	HR.JOB_HISTORY
Description	Table that stores job history of the employees. If an employee changes departments within the job or changes jobs within the department, new rows get inserted into this table with old job information of the employee. Contains a complex primary key: employee_id+start_date. Contains 25 rows. References with jobs, employees, and departments tables.

Columns

Column	Data Type	Nullable	Default	Description
EMPLOYEE_ID	NUMBER(6, 0)	not null		A not null column in the complex primary key employee_id+start_date. Foreign key to employee_id column of the employee table
START_DATE	DATE(7)	not null		A not null column in the complex primary key employee_id+start_date. Must be less than the end_date of the job_history table. (enforced by constraint jhist_date_interval)
END_DATE	DATE(7)	not null		Last day of the employee in this job role. A not null column. Must be greater than the start_date of the job_history table. (enforced by constraint jhist_date_interval)
JOB_ID	VARCHAR2(10)	not null		Job role in which the employee worked in the past; foreign key to job_id column in the jobs table. A not null column.
DEPARTMENT_ID	NUMBER(4, 0)	null		Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table

Primary Key

Primary Key	Columns
JHIST_EMP_ID_ST_DATE_PK	EMPLOYEE_ID, START_DATE

Indexes

Index	Type	Columns
JHIST_DEPARTMENT_IX		DEPARTMENT_ID
JHIST_EMPLOYEE_IX		EMPLOYEE_ID
JHIST_JOB_IX		JOB_ID

Check Constraints

Check Constraint	Expression	Description
JHIST_DATE_INTERVAL	end_date > start_date
JHIST_EMPLOYEE_NN	"EMPLOYEE_ID" IS NOT NULL
JHIST_END_DATE_NN	"END_DATE" IS NOT NULL
JHIST_JOB_NN	"JOB_ID" IS NOT NULL
JHIST_START_DATE_NN	"START_DATE" IS NOT NULL

Foreign Keys

Relation	Column	Referenced Column
HR.DEPARTMENTS	DEPARTMENT_ID	DEPARTMENT_ID
HR.EMPLOYEES	EMPLOYEE_ID	EMPLOYEE_ID
HR.JOBS	JOB_ID	JOB_ID