--**********************************************************************00010000
--*  MEMBER NAME: CONSTR                                               *00020000
--*                                                                    *00030000
--*  DESCRIPTION: CEJCO CLASS RI AND CHECK CONSTRAINTS SET-UP          *00040000
--*                                                                    *00050000
--*  CHANGE ALL:  SCHEMA??     TO YOUR TABLE SCHEMA (CREATOR) NAME     *00060000
--**********************************************************************00070000
--                                                                      00080000
  SET CURRENT RULES = 'STD';                                            00090000
--                                                                      00100000
  ALTER TABLE SCHEMA??.EMPLOYEE                                         00110000
    ADD CONSTRAINT INVALID_SEX CHECK (SEX IN ('M','F'));                00120000
  ALTER TABLE SCHEMA??.EMPLOYEE                                         00130000
    ADD CONSTRAINT EXCESSIVE_BONUS CHECK (BONUS <= SALARY);             00140000
  ALTER TABLE SCHEMA??.PROJECT                                          00150000
    ADD CONSTRAINT START_AFTER_END CHECK (PRSTDATE <= PRENDATE);        00160000
  ALTER TABLE SCHEMA??.EMP_ACT                                          00170000
    ADD CONSTRAINT START_AFTER_END CHECK (EMSTDATE <= EMENDATE);        00180000
  COMMIT;                                                               00190000
--                                                                      00200000
  ALTER TABLE SCHEMA??.DEPARTMENT                                       00210000
    ADD FOREIGN KEY RDE (MGRNO)                                         00220000
        REFERENCES SCHEMA??.EMPLOYEE                                    00230000
        ON DELETE SET NULL;                                             00240000
  ALTER TABLE SCHEMA??.DEPARTMENT                                       00250000
    ADD FOREIGN KEY RDD (ADMRDEPT)                                      00260000
        REFERENCES SCHEMA??.DEPARTMENT                                  00270000
        ON DELETE CASCADE;                                              00280000
  ALTER TABLE SCHEMA??.EMPLOYEE                                         00290000
    ADD FOREIGN KEY RED (WORKDEPT)                                      00300000
        REFERENCES SCHEMA??.DEPARTMENT                                  00310000
        ON DELETE SET NULL;                                             00320000
  ALTER TABLE SCHEMA??.PROJECT                                          00330000
    ADD FOREIGN KEY RPD (DEPTNO)                                        00340000
        REFERENCES SCHEMA??.DEPARTMENT                                  00350000
        ON DELETE RESTRICT;                                             00360000
  ALTER TABLE SCHEMA??.PROJECT                                          00370000
    ADD FOREIGN KEY RPE (RESPEMP)                                       00380000
        REFERENCES SCHEMA??.EMPLOYEE                                    00390000
        ON DELETE RESTRICT;                                             00400000
  ALTER TABLE SCHEMA??.EMP_ACT                                          00410000
    ADD FOREIGN KEY RAE (EMPNO)                                         00420000
        REFERENCES SCHEMA??.EMPLOYEE                                    00430000
        ON DELETE RESTRICT;                                             00440000
  ALTER TABLE SCHEMA??.EMP_ACT                                          00450000
    ADD FOREIGN KEY RAP (PROJNO)                                        00460000
        REFERENCES SCHEMA??.PROJECT                                     00470000
        ON DELETE CASCADE;                                              00480000
  COMMIT;                                                               00490000
--                                                                      00500000
  SET CURRENT RULES = 'DB2';                                            00510000