지식인에 오라클 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
) ;
'SQL' 카테고리의 다른 글
[MySQL] order by 정렬 (0) | 2016.01.26 |
---|---|
MySQL Update Where 조건절 Subquery (1) | 2016.01.22 |
[MySQL] 두개의 테이블의 데이터 조인하여 내용 Update (0) | 2015.09.16 |
[MySQL] DataDiff 함수 기능 테스를 위한 테이블 생성과 Query (0) | 2015.09.13 |
[MySQL] explain 실행계획(SQL 성능 튜닝) ★★★★ (0) | 2015.08.21 |