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 같이 보기

• MySQL 외래키 옵션 변경
• 외래키