MySQL 테이블간 참조무결성

지식인에 오라클 SQL 인데 MYSQL 에서 에러가 발생한다는 질문이 있어서 테이블을 생성해봤다.

CREATE TABLE IF NOT EXISTS `department` (
`DEPTNO` int(11) unsigned NOT NULL,
`DEPTNAME` varchar(10) DEFAULT NULL,
`FLOOR` int(11) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO DEPARTMENT VALUES(1, '영업', 8);
INSERT INTO DEPARTMENT VALUES(2, '기획', 10);
INSERT INTO DEPARTMENT VALUES(3, '개발', 9);
INSERT INTO DEPARTMENT VALUES(4, '총무', 7);

-- EMPLOYEE 테이블 생성
CREATE TABLE IF NOT EXISTS `employee` (
  `EMPNO` int(11) unsigned NOT NULL,
  `EMPNAME` varchar(10) DEFAULT NULL,
  `TITLE` varchar(10) DEFAULT '사원',
  `MANAGER` int(11) DEFAULT NULL,
  `SALARY` int(11) NOT NULL,
  `DNO` int(5) NOT NULL DEFAULT '1',
  PRIMARY KEY (EMPNO),
  CHECK(SALARY < 6000000),
  CHECK (DNO IN (1,2,3,4)),
  FOREIGN KEY(MANAGER) REFERENCES employee(EMPNO) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(DNO) REFERENCES department(DEPTNO) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO EMPLOYEE VALUES(4377, '이성래', '사장', NULL, 5000000, 2);
INSERT INTO EMPLOYEE VALUES(3426, '박영권', '과장', 4377, 3000000, 1);
INSERT INTO EMPLOYEE VALUES(3011, '이수민', '부장', 4377, 4000000, 3);
INSERT INTO EMPLOYEE VALUES(3427, '최종철', '사원', 3011, 1500000, 3);
INSERT INTO EMPLOYEE VALUES(1003, '조민희', '과장', 4377, 3000000, 2);
INSERT INTO EMPLOYEE VALUES(2106, '김창섭', '대리', 1003, 2500000, 2);
INSERT INTO EMPLOYEE VALUES(1365, '김상원', '사원', 3426, 1500000, 1);

오라클 EMPLOYEE 테이블 생성
CREATE TABLE IF NOT EXISTS `employee` (
  `EMPNO` int(11) unsigned NOT NULL,
  `EMPNAME` varchar(10) DEFAULT NULL,
  `TITLE` varchar(10) DEFAULT '사원',
  `MANAGER` int(11) DEFAULT NULL,
  `SALARY` int(11) NOT NULL,
  `DNO` int(5) NOT NULL DEFAULT '1',
  PRIMARY KEY (EMPNO),
  check(SALARY < 6000000),
  CHECK (DNO IN (1,2,3,4)),
  FOREIGN KEY(MANAGER) REFERENCES employee(EMPNO)ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(DNO) REFERENCES department(DEPTNO) ON DELETE CASCADE ON UPDATE CASCADE
) ;