SQL_05(Foreign key, JOIN 이론)

테이블 간의 관계 : Foreign key 제약조건

▪ Foreign key(외래키) – 한 테이블이 다른 테이블의 컬럼 값을 참조하는 것

▪ 부모테이블 – 참조 되는 테이블

▪ 자식테이블 – 참조 하는 테이블

– 직원테이블의 데이터(ROW)는 부서테이블의 데이터를 참조한다.

– 부서테이블의 ROW는 직원테이블의 데이터 0개~N개가 참조할 수 있다.

– 직원테이블의 데이터 하나는 직원테이블의 데이터 하나를 상사_ID로 참조한다.

 

▪ 기본 구문

CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERECES 부모테이블(PK컬럼) [ON 설정]

    – 참조컬럼은 PK거나 Index 설정된 컬럼이어야 한다.

▪ 자식 테이블로부터 참조 당하는 부모 테이블의 row는 삭제 할 수 없다.

▪ CASCADE DELETE

    – FOREIGN KEY 설정 시 ON DELETE CASCADE 설정

        ▪ 부모 테이블의 참조 ROW 삭제 시 자식 테이블의 참조 ROW 삭제

CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERECES 부모테이블(PK컬럼) ON DELETE CASCADE

 

    – FOREIGN KEY 설정 시 ON DELETE SET NULL설정

        ▪ 부모 테이블의 참조 ROW 삭제 시 참조 하는 자식 테이블의 컬럼 값을 NULL로 설정

 CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERECES 부모테이블(PK컬럼) ON DELETE SET NULL

 

▪ ON 설정

– 기본적으로 자식테이블에서 참조하고 있으면 부모 테이블의 데이터를 삭제/수정하지 못한다.

▪ Foreign key 컬럼 설정할 때 이것을 변경할 수 있는데 이때 on 설정을 사용한다.

– ON DELETE|UPDATE 처리방식

– ON DELETE: 참조하는 부모테이블의 행이 삭제 되었을 때 어떻게 처리할 것인지 설정

– ON UPDATE: 참조하는 부모테이블의 참조 컬럼값이 변경되면 어떻게 처리할 것인지 설정

– 처리방식

▪ CASCADE : 부모 테이블에서 데이터를 삭제/수정하면, 그 행을 참조하는 자식테이블의 행들도 같이 삭제/수정된다.

▪ SET NULL : 부모 테이블에서 데이터를 삭제/수정하면, 그 행을 참조하는 자식테이블의 foreign key 컬럼의 값을 NULL로 변경한다.

 

▪ Table drop시 foreign key 제약조건 제거

    ▪ 부모 테이블 DROP전에 MySQL의 foreign key 적용이 안되도록 설정해야 한다.

set foreign_key_ckecks = 0; -- foreign key 해제 설정

DROP table 삭제할테이블;

set foreign_key_ckecks = 1; -- foreign key 적용 설정

 

조인 개념

▪ 관계 있는 두개의 테이블을 합쳐서(JOIN) 조회(SELECT) 하는 것을 말한다.

▪ 조인할 두 테이블의 특정 컬럼의 값들이 같은 행끼리 합친다.

▪ 조인연산

– 어떤 행들의 값이 같은 것끼리 합칠 것인지 설정하는 구문.

– 일반적으로 부모테이블과 자식테이블을 연결해서 조회하는 경우가 많으며 이 경우 부모 테이블의 PK와 자식 테이블의 FK 컬럼의 값이 같은 행들을 JOIN 한다.

▪ 카티션 곱(Cartesian-Product)

– 두 테이블의 전체 행들을 합칠 수 있는 모든 경우의 조합이 만들어지는 경우.

▪ N행, M행 의 두 테이블 조인시 N x M 개의 행이 생성된다.

– 이유

▪ 조인 연산을 생략한 경우(CROSS JOIN)

▪ 조인 연산이 잘못된 경우 ▪ 조인 종류

– INNER JOIN

▪ 조인 연산 조건을 만족하는 행들을 합친다.

– OUTER JOIN

▪ 조인 연산 조건을 만족하지 않는 행들도 포함해서 합친다.

▪ LEFT OUTER JOIN

▪ RIGHT OUTER JOIN

▪ FULL OUTER JOIN

 

INNER JOIN(내부조인)

▪ 조인 연산 조건을 만족하는 행끼리 만 합치는 JOIN

▪ 구문

SELECT 컬럼선택
FROM 테이블_1 INNER JOIN 테이블_2 ON 조인연산 -- INNER 생략가능
             [INNER JOIN 테이블_3 ON 조인연산 …]

SELECT b.부서_ID, b.부서이름, 
       e.직원이름
FROM 부서 b INNER JOIN 직원 e ON b.소속부서=e.부서_ID

조인 – 외부조인(Outer Join)

▪ 조인 연산 조건을 만족하지 않는 행도 포함해서 합친다.

▪ Source Table

– 조인시 조회 기준이 되는 테이블로 조회하려는 주(main) 정보를 가지고 있는 테이블

▪ Target Table

– 조인시 Source table 데이터의 추가 정보를 가지고 있는 테이블로 보조(sub) 정보를 가지고 있는 테이블

▪ 조인시 Source 테이블의 데이터(행)은 모두 사용하고 Target table의 데이터(행)은 조인 조건 을 만족하는 행만 나오도록 한다.

▪ Outer Join 종류

– LEFT OUTER JOIN

▪ Source 테이블이 왼쪽에 선언한 테이블인 경우의 outer join.

– RIGHT OUTER JOIN

▪ Source 테이블이 오른쪽에 선언한 테이블인 경우의 outer join.

– FULL OUTER JOIN

▪ 양쪽에 선언한 테이블이 모두 Source table인 경우의 outer join ▪ MySQL은 지원하지 않는다.

 

▪ LEFT OUTER JOIN

SELECT 컬럼선택
FROM 테이블_1 LEFT [OUTER] JOIN 테이블_2 ON 조인연산 -- OUTER 생략가능
-- Source table: 테이블_1, Target table: 테이블_2

 

SELECT b.부서_ID, b.부서이름, 
       e.직원이름
FROM 부서 b LEFT OUTER JOIN 직원 e ON b.소속부서=e.부서_ID

▪ RIGHT OUTER JOIN

SELECT 컬럼선택
FROM 테이블_1 RIGHT [OUTER] JOIN 테이블_2 ON 조인연산 -- OUTER 생략가능
-- Source table: 테이블_2, Target table: 테이블_1

SELECT b.부서_ID, b.부서이름, 
       e.직원이름
FROM 부서 b RIGHT OUTER JOIN 직원 e ON b.소속부서=e.부서_ID

▪ FULL OUTER JOIN

– MySQL은 지원하지 않는다.

– left join, right join select 문을 union 하여 구현한다.

SELECT b.부서_ID, b.부서이름, e.직원이름
FROM 부서 b LEFT OUTER JOIN 직원 e ON b.소속부서=e.부서_ID
UNION
SELECT b.부서_ID, b.부서이름, e.직원이름
FROM 부서 b RIGHT OUTER JOIN 직원 e ON b.소속부서=e.부서_ID;