교착 상태(Dead lock) 테이블 구성하기

제가 쫌 거시기 하는 O* 데이터베이스의 HR을 보면서 재밌는 장면이 나왔습니다.

Employees 테이블은 Departments 를 참조하고 있고, Departments 테이블은 Employees의 Manager_id를 Foreign Key로 잡고 있습니다.

툴을 통한 DDL을 보면 이렇게 나옵니다.

  CREATE TABLE "DEPARTMENTS" 
   (        "DEPARTMENT_ID" NUMBER(4,0), 
        "DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE, 
        "MANAGER_ID" NUMBER(6,0), 
        "LOCATION_ID" NUMBER(4,0), 
         CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID") ENABLE, 
         CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID")
          REFERENCES "LOCATIONS" ("LOCATION_ID") ENABLE,
         CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "EMPLOYEES" ("MANAGER_ID") ENABLE
   );
  
  
  CREATE TABLE "EMPLOYEES" 
   (        "EMPLOYEE_ID" NUMBER(6,0), 
        "FIRST_NAME" VARCHAR2(20), 
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, 
        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, 
        "PHONE_NUMBER" VARCHAR2(20), 
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, 
        "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, 
        "SALARY" NUMBER(8,2), 
        "COMMISSION_PCT" NUMBER(2,2), 
        "MANAGER_ID" NUMBER(6,0), 
        "DEPARTMENT_ID" NUMBER(4,0), 
         CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, 
         CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") ENABLE, 
         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE, 
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "DEPARTMENTS" ("DEPARTMENT_ID") ENABLE,
         CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "EMPLOYEES" ("EMPLOYEE_ID") ENABLE
   );

이럴 때는 Departments의 Employees 참조를 빼고, 생성한 다음, Alter로 Foreign Key를 연결하면 가능합니다.

  CREATE TABLE "DEPARTMENTS" 
   (        "DEPARTMENT_ID" NUMBER(4,0), 
        "DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE, 
        "MANAGER_ID" NUMBER(6,0), 
        "LOCATION_ID" NUMBER(4,0), 
         CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID") ENABLE, 
         CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID")
          REFERENCES "LOCATIONS" ("LOCATION_ID") ENABLE
   );
  
  
  CREATE TABLE "EMPLOYEES" 
   (        "EMPLOYEE_ID" NUMBER(6,0), 
...   );
   
  ALTER TABLE "DEPARTMENTS" ADD CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "EMPLOYEES" ("EMPLOYEE_ID") ENABLE;

고마워요. 동인 아저씨 😀👍