! Copyright © Oracle Corporation 1995.  All Rights Reserved.
!----------------------------------------------
! Define Constraints
!----------------------------------------------
!
START_TRANSACTION READ_WRITE
PRINT "Defining constraints for the sample database"
!
! Employee ID from JOB_HISTORY must exist in EMPLOYEES
!   relation exists before it can be stored in JOB_HISTORY
!
DEFINE CONSTRAINT JH_EMP_ID_EXISTS
   FOR JH IN JOB_HISTORY
   REQUIRE ANY E IN EMPLOYEES WITH
      E.EMPLOYEE_ID = JH.EMPLOYEE_ID
   CHECK ON COMMIT.
!
! 
! Employee ID from SALARY_HISTORY must exist in EMPLOYEES
!   relation exists before it can be stored in SALARY_HISTORY
!
DEFINE CONSTRAINT SH_EMP_ID_EXISTS
   FOR SH IN SALARY_HISTORY
   REQUIRE ANY E IN EMPLOYEES WITH
      E.EMPLOYEE_ID = SH.EMPLOYEE_ID
   CHECK ON COMMIT.
!
! There must be an EMPLOYEE_ID (primary key) for each EMPLOYEE record
!
DEFINE CONSTRAINT EMPLOYEE_ID_REQUIRED
   FOR E IN EMPLOYEES
   REQUIRE NOT E.EMPLOYEE_ID MISSING.
!
! There must be a DEPARTMENT_CODE (primary key) for each DEPARTMENT record
!
DEFINE CONSTRAINT DEPT_CODE_REQUIRED
   FOR D IN DEPARTMENTS
   REQUIRE NOT D.DEPARTMENT_CODE MISSING.
!
! There must be JOB_CODE (primary key) for each JOBS record
!
DEFINE CONSTRAINT JOB_CODE_REQUIRED
   FOR J IN JOBS
   REQUIRE NOT J.JOB_CODE MISSING.
!
! There must be COLLEGE_CODE (primary key) for each COLLEGES record
!
DEFINE CONSTRAINT COLLEGE_CODE_REQUIRED
   FOR C IN COLLEGES
   REQUIRE NOT C.COLLEGE_CODE MISSING.
!
!----------------------------------------------
! You can have similar constraints for all required fields.
! Note that these constraints assume a certain order for loading
! data.  You cannot store data into JOB_HISTORY until EMPLOYEES
! is loaded, and so on.
!----------------------------------------------
COMMIT