MySQL 외래키 생성

MySQL 외래키 생성 (기본)
ON DELETE RESTRICT, ON UPDATE RESTIRCT
  • 외래키 옵션을 주지 않으면 기본적으로 변경 제약, 삭제 제약이 걸린다.
자식테이블이 참조하는 값에 대해서는 삭제 및 변경이 금지된다.

1 DB 스키마 간단히[ | ]

한국어
직원 { 직원번호, 부서번호, 직원명}
부서 { 부서번호, 부서명 }
영어 약자
emp { emp_no, dept_no, emp_name }
dept { dept_no, dept_name }

2 DB 스키마 구체화[ | ]

emp
컬럼명 자료형
emp_no(PK) 양수(11)
dept_no(FK) 양수(11)
emp_name 문자(32)
dept
컬럼명 자료형
dept_no(PK) 양수(11)
dept_name 문자(32)

3 테이블 생성[ | ]

부서 테이블 생성
CREATE TABLE `dept` (
  `dept_no` int(11) unsigned NOT NULL,
  `dept_name` varchar(32) NOT NULL,
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
직원 테이블 생성
CREATE TABLE `emp` (
  `emp_no` int(11) unsigned NOT NULL,
  `dept_no` int(11) unsigned NOT NULL,
  `emp_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`emp_no`),
  FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 외래키에 옵션을 주지 않았다. 그러면 기본적으로 삭제와 변경이 제한된다.

4 샘플 데이터[ | ]

부서 테이블
1
2
3
dept_no dept_name
1001 인사부
1002 영업부
1003 생산부
직원 테이블
1
2
3
emp_no dept_no emp_name
2012001 1001 한놈
2012002 1003 두시기
2012003 1003 석삼
→ 한놈은 부서번호가 1001이므로 '인사부'
→ 두시기, 석삼은 부서번호가 1003이므로 '생산부'

5 데이터 입력[ | ]

INSERT INTO dept (dept_no, dept_name) VALUES ('1001', '인사부');
INSERT INTO dept (dept_no, dept_name) VALUES ('1002', '영업부');
INSERT INTO dept (dept_no, dept_name) VALUES ('1003', '생산부');

INSERT INTO emp (emp_no, dept_no, emp_name) VALUES ('2012001', '1001', '한놈');
INSERT INTO emp (emp_no, dept_no, emp_name) VALUES ('2012002', '1003', '두시기');
INSERT INTO emp (emp_no, dept_no, emp_name) VALUES ('2012003', '1003', '석삼');

6 데이터 변경/삭제[ | ]

-- 갱신
UPDATE dept SET dept_no='1004' WHERE dept_no='1003';
-- 삭제
DELETE FROM dept WHERE dept_no='1003';

'생산부'의 부서번호 1003을 변경하거나 삭제하려고 하면 다음과 같은 오류가 발생한다.

#1451 - Cannot delete or update a parent row:
a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`))

직원테이블에서 참조하지 않는 1002는 당연히 삭제 가능하다.

DELETE FROM dept WHERE dept_no='1002'
-- 1 row deleted. ( Query took 0.0018 sec )

7 같이 보기[ | ]

문서 댓글 ({{ doc_comments.length }})
{{ comment.name }} {{ comment.created | snstime }}