Department table (DSN8A10.DEPT)

The department table resides in table space DSN8D10A.DSN8S10D and is created with the following statement:

CREATE TABLE DSN8A10.DEPT
      (DEPTNO    CHAR(3)           NOT NULL,
       DEPTNAME  VARCHAR(36)       NOT NULL,
       MGRNO     CHAR(6)                   ,
       ADMRDEPT  CHAR(3)           NOT NULL,
       LOCATION  CHAR(16)                  ,
       PRIMARY KEY (DEPTNO)                )
  IN DSN8D10A.DSN8S10D
  CCSID EBCDIC;

Because the department table is self-referencing, and also is part of a cycle of dependencies, its foreign keys must be added later with the following statements:

ALTER TABLE DSN8A10.DEPT
      FOREIGN KEY RDD (ADMRDEPT) REFERENCES DSN8A10.DEPT
              ON DELETE CASCADE;
 
ALTER TABLE DSN8A10.DEPT
      FOREIGN KEY RDE (MGRNO) REFERENCES DSN8A10.EMP
              ON DELETE SET NULL;