MySQL 외래키 생성 문서 원본 보기

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

==DB 스키마 간단히==
;한국어
<syntaxhighlight lang='text'>
직원 { 직원번호, 부서번호, 직원명}
부서 { 부서번호, 부서명 }
</syntaxhighlight>

;영어 약자
<syntaxhighlight lang='text'>
emp { emp_no, dept_no, emp_name }
dept { dept_no, dept_name }
</syntaxhighlight>

==DB 스키마 구체화==
{| class="wikitable"
|+emp
!컬럼명 || 자료형
|-
|emp_no(PK) || 양수(11)
|-
|dept_no(FK) || 양수(11)
|-
|emp_name || 문자(32)
|}

{| class="wikitable"
|+dept
!컬럼명 || 자료형
|-
|dept_no(PK) || 양수(11)
|-
|dept_name || 문자(32)
|}

==테이블 생성==
;부서 테이블 생성
<syntaxhighlight lang='sql'>
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;
</syntaxhighlight>

;직원 테이블 생성
<syntaxhighlight lang='sql'>
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;
</syntaxhighlight>
*[[외래키]]에 옵션을 주지 않았다. 그러면 기본적으로 삭제와 변경이 제한된다.

==샘플 데이터==
;부서 테이블
{{엑셀 행| 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이므로 '생산부'

==데이터 입력==
<syntaxhighlight lang='sql'>
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', '석삼');
</syntaxhighlight>

==데이터 변경/삭제==
<syntaxhighlight lang='sql'>
-- 갱신
UPDATE dept SET dept_no='1004' WHERE dept_no='1003';
-- 삭제
DELETE FROM dept WHERE dept_no='1003';
</syntaxhighlight>

'생산부'의 부서번호 1003을 변경하거나 삭제하려고 하면 다음과 같은 오류가 발생한다.
<syntaxhighlight lang='text'>
#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`))
</syntaxhighlight>

직원테이블에서 참조하지 않는 1002는 당연히 삭제 가능하다.
<syntaxhighlight lang='sql'>
DELETE FROM dept WHERE dept_no='1002'
-- 1 row deleted. ( Query took 0.0018 sec )
</syntaxhighlight>

==같이 보기==
*[[MySQL 외래키 옵션 변경]]
*[[외래키]]

[[분류: MySQL]]