현대 오토에버 클라우드 스쿨

데이터베이스

Gom3rye 2025. 5. 20. 10:07

JOIN

테이블 구조

→ EMP 테이블과 DEPT 테이블은 부서 번호인 DEPTNO 컬럼을 모두 가지고 있다. → EMP 테이블: 데이터가 14개, 열이 8개 → DEPT 테이블: 데이터가 4개, 열이 3개

CROSS JOIN

  • 별다른 조인 조건 없이 FROM 절에 2개 테이블 이상을 나열
  • 2개 테이블의 모든 조합이 나온다.
  • 왠만해서는 하지 말아라! ⇒ 데이터 너무 많이 나와서
SELECT *
FROM EMP, DEPT; # (= FROM EMP CROSS JOIN DEPT;)

# 56개의 데이터(ROWS), 11개의 열
# 행의 개수는 곱한 것과 같고 열의 개수는 더한 것과 같다.

EQUI JOIN

  • 2개 테이블에 동일한 의미를 갖는 컬럼이 있는 경우 두 개의 컬럼이 같은 값을 갖는 데이터만 조인
  • 제일 많이 쓰이는 조인
  • WHERE절에 조인 조건 기술
  • 컬럼의 이름이 동일한 경우 테이블이름.컬럼이름으로 조인 조건을 기술해야 한다.
select *
from EMP, DEPT
where EMP.DEPTNO = DEPT.DEPTNO;
# 행: 14(가장 많은 거 따라감), 열: 11개(둘이 합침)
  • 조건절에 추가 가능

ex. ENAME이 MILLER인 사원의 부서 정보 추출

select ENAME, DNAME
from EMP, DEPT
where EMP.DEPTNO = DEPT.DEPTNO AND UPPER(ENAME)='MILLER';

⇒ 주의) 동일한 컬럼 사용 시 테이블 이름 꼭 기술해야 한다!!

SELECT DEPTNO, ENAME, DNAME # 에러O, DEPTNO가 양쪽에 다 있어서 어느 걸 출력해야 하는지 모름
SELECT EMP.DEPTNO, ENAME, DNAME #-> GOOD
  • FROM 절에서 테이블 이름을 변경하는 것 가능 (테이블 이름 뒤에 다른 이름을 작성하면 된다.) → 이때 변경하는 이름은 별명이 아니다.
select D.DEPTNO, ENAME, DNAME
from EMP E, DEPT D
where E.DEPTNO = D.DEPTNO AND UPPER(ENAME)='MILLER';
# 이때 DEPT.DEPTNO 라고 하면 에러난다. D로 이름을 바꿨으니 D.DEPTNO 라고 해야 함
  • 연습 문제
    • DEPT 테이블의 LOC이 ‘NEW YORK’인 사원의 EMP 테이블의 이름(ENAME)과 급여(SAL)를 조회
    select E.ENAME, E.SAL
from EMP E, DEPT D
where E.DEPTNO = D.DEPTNO AND UPPER(LOC)='NEW YORK';

## OR SUB QUERY로도 해결 가능
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE UPPER(LOC)='NEW YORK');
    • DEPT 테이블의 DNAME 컬럼의 값이 ‘ACCOUNTING’인 사원의 EMP 테이블의 이름(ENAME)과 입사일(HIREDATE)을 조회
    select E.ENAME, E.HIREDATE
from EMP E, DEPT D
where E.DEPTNO = D.DEPTNO AND UPPER(DNAME)='ACCOUNTING';
## OR
WHERE ENAME, HIREDATE
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE UPPER(DNAME)='ACCOUNTING');
    • JOIN은 회피하는 게 좋다. → 테이블 너무 커지니까
    • ⇒ 조회하고자 하는 컬럼들이 하나의 테이블에 존재하는 경우는 조인 대신 서브 쿼리 이용해라!

NON EQUI JOIN

  • = 이외의 연산자를 가지고 조인하는 것

ex. EMP 테이블의 ENAME과 SAL, 그리고 SALGRADE 테이블의 GRADE를 조회 (⇒ 조회하고자 하는 컬럼들이 하나의 테이블에 존재 X → 서브 쿼리 이용 못함)

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;
## OR
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELF JOIN

  • 하나의 테이블을 가지고 JOIN
  • 하나의 테이블에 동일한 의미를 가진 컬럼이 2개 이상 존재하는 경우 사용
  • 동일한 테이블을 2번 사용하기 때문에 반드시 테이블에 새로운 이름을 부여해야 한다.
  • ex. 사원 이름과 관리자의 이름을 동시에 조회하고자 하는 경우 관리자 사원 번호를 조회한 후 다시 이를 이용해서 관리자 사원 이름을 조회해야 한다. (EMP 테이블의 EMPNO는 사원 번호이고 MGR은 관리자 사원 번호)
# 사원 이름과 관리자 이름 조회
SELECT E1.ENAME '사원 이름', E2.ENAME '관리자 이름'
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

SELECT ENAME, SAL
FROM EMP
WHERE MGR = (SELECT EMPNO FROM EMP WHERE UPPER(ENAME) = 'KING'); #7839
# 계속 붙어있는지 확인해야 하니까 이 쿼리는 인사 업무에서 굉장히 많이 사용된다.
# SELF JOIN

ex. EMP 테이블에서 MANAGER가 KING인 사원의 ENAME과 JOB 조회

# SELF JOIN 사용
SELECT E1.ENAME '사원 이름', E1.JOB '직업'
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO AND UPPER(E2.ENAME) = 'KING';

# SUB QUERY 사용
SELECT ENAME '사원 이름', JOB '직업'
FROM EMP
WHERE MGR = (SELECT EMPNO FROM EMP WHERE UPPER(ENAME) = 'KING');
  • SNS의 친구 추천 서비스, 장바구니의 추천 시스템(이걸 산 사람들이 많이 산 거) 등을 만들 때 이 방식 쓴다.

INNER JOIN

  • EQUI JOIN과 결과가 동일

FROM 테이블이름 INNER JOIN 테이블이름 ON 조인조건 → (조인 조건이랑 일반 조건이랑 구분하는 것)

ex. ENAME이 MILLER인 사원의 ENAME(EMP 테이블)과 DNAME(DEPT 테이블) 조회

SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND ENAME = 'MILLER';

SELECT E.ENAME, D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE ENAME = 'MILLER';
  • 2개 테이블의 조인 컬럼의 이름이 같은 경우는 ON 대신에 USING (컬럼이름)을 사용해도 된다.
SELECT E.ENAME, D.DNAME
FROM EMP E INNER JOIN DEPT D
USING (DEPTNO)
WHERE ENAME = 'MILLER';

NATURAL JOIN

  • 2개 테이블의 조인 컬럼의 이름이 같은 경우 INNER 대신에 NATURAL 이라고 기재하고 조인 조건을 생략
SELECT E.ENAME, D.DNAME
FROM EMP E NATURAL JOIN DEPT D
WHERE ENAME = 'MILLER';

OUTER JOIN

  • 어느 한 쪽 테이블에만 존재하는 데이터도 JOIN에 참여하는 것
  • INNER JOIN이나 EQUI JOIN은 양쪽 테이블에 모두 존재하는 데이터만 JOIN에 참여
  • LEFT (왼쪽만 가져오기)
  • RIGHT
  • FULL (양쪽 가져오기) → Maria DB에는 full outer join 지원 X → SET 연산(UNION)을 이용해 구현
select DISTINCT(DEPTNO) # distinct: 중복 제거 
from EMP;   # deptno: 10, 20, 30

select DEPTNO
from DEPT;   # deptno: 10, 20, 30, 40

select DISTINCT(DEPT.DEPTNO)
from DEPT inner join EMP
on DEPT.DEPTNO = EMP.DEPTNO; # deptno: 10, 20, 30

select *
from DEPT left outer join EMP
on DEPT.DEPTNO = EMP.DEPTNO # deptno: 10, 20, 30, 40
union
select *
from DEPT right outer join EMP
on DEPT.DEPTNO = EMP.DEPTNO;  # FULL OUTER JOIN 결과 deptno: 10, 20, 30, 40
  • 연습문제
    • EMP 테이블에서 모든 사원에 대한 이름(ENAME), 부서번호(DEPTNO) DEPT 테이블에서 부서명(DNAME)을 가져와서 출력하는 SELECT문 작성
    SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E INNER JOIN DEPT D
USING (DEPTNO);

# OR
SELECT E.ENAME, E.DEPTNO, D.DNAME
from EMP E, DEPT D
where E.DEPTNO = D.DEPTNO;
    • DEPT 테이블의 LOC가 NEW YORK에서 근무하고 있는 사원에 대해 EMP 테이블의 이름(ENAME), 업무(JOB), 급여(SAL), DEPT 테이블의 부서명(DNAME)을 출력하는 SELECT문 작성
    SELECT E.ENAME, E.JOB, E.SAL, D.DNAME
FROM EMP E INNER JOIN DEPT D
USING (DEPTNO)
WHERE D.LOC = 'NEW YORK';
    • EMP 테이블에서 보너스(COMM)가 NULL이 아닌 사원에 대해 이름(ENAME), DEPT 테이블의 부서명(DNAME), 위치(LOC)를 출력하는 SELECT문 작성
    SELECT E.ENAME, D.DNAME, D.LOC
FROM EMP E INNER JOIN DEPT D
USING (DEPTNO)
WHERE E.COMM IS NOT NULL;
    • EMP 테이블에서 이름(ENAME) 중 L자가 있는 사원에 대해 이름(ENAME), 업무(JOB), DEPT 테이블의 부서명(DNAME), 위치(LOC)를 출력하는 SELECT문 작성
    SELECT E.ENAME, E.JOB, D.DNAME, D.LOC
FROM EMP E INNER JOIN DEPT D
USING (DEPTNO)
WHERE E.ENAME LIKE '%L%';
    • EMP 테이블에서 그들의 관리자(MGR) 보다 먼저 입사한 사원에 대해 이름(ENAME), 입사일(HIREDATE), 관리자(MGR) 이름, 관리자(MGR) 입사일을 출력하는 SELECT문 작성
    SELECT E1.ENAME '사원 이름', E1.HIREDATE, E2.ENAME '관리자 이름', E2.HIREDATE
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO AND E1.HIREDATE < E2.HIREDATE; # 날짜 크기 비교 가능

DDL

N:N의 관계: 쇼핑몰에서 고객 ↔ 상품 (이러면 새 테이블 만들어진다.) ⇒ 양쪽의 기본 키를 가지고 와서 새 테이블 만들자.

테이블 설계 시 주의해야 할 점

이상 현상: 데이터베이스에 작업을 할 때 발생하는 문제점

  • 삽입 이상: 데이터를 삽입하고자 하는데 삽입하지 못하는 현상 ex. 기본키는 NULL 이 될 수 없는데 NULL인 값을 넣으려고 할 때
  • 삭제 이상: 데이터를 삭제할 때 필요한 정보도 같이 삭제되는 현상
  • 변경 이상: 데이터를 수정할 때 한 번만 수정하면 되는데 여러 번 수정해야 하는 현상

→ 이상 현상은 보통 테이블을 너무 크게 설계해서 발생한다. (데이터 중복으로 인해 발생)

ex. [(PK)전화번호+수강과목, 이름, 주소, 수강료] ⇒ [(PK)전화번호, 이름 주소] | [(PK)수강과목, 수강료] | [전화번호, 수강과목]

→ 정규화: 테이블이 커서 이상 현상이 발생하지 않도록 테이블을 쪼개는 것

함수적 종속

  • 어느 하나의 속성이 다른 속성의 데이터를 1개로 구별할 때 함수적으로 종속한다고 한다.

ex. 주민등록번호를 알면 이름을 1개로 결정할 수 있다. ⇒ ‘주민등록번호는 이름을 함수적으로 종속한다.’라고 하고 ‘주민등록번호 → 이름’으로 표기한다.

  • 종류
  • 완전함수적종속: 기본키가 여러 개의 속성으로 구성될 때 기본키 전체를 알아야 하나의 값을 결정할 수 있는 경우
  • 부분함수적종속: 기본키가 여러 개의 속성으로 구성될 때 기본키의 일부분만으로 하나의 값을 결정할 수 있는 경우
  • 이행적함수적종속: 하나의 속성이 다른 하나의 속성의 값을 결정하고 결정되는 속성이 또 다른 속성의 값을 결정하는 경우

정규화

  • 논리 데이터 모델을 일관성이 있고 안정성 있는 자료 구조로 만드는 과정
  • 데이터의 일관성, 최소한의 중복, 최대한의 데이터 유연성을 확보하기 위한 과정으로 데이터를 분해하는 과정
  • 장점
    • 중복 값이 줄어든다.
    • NULL 값이 줄어든다.
    • 데이터 구조의 안정성을 최대화한다.
    • 삽입, 삭제, 갱신에 유리하다.
  • 정규형
    • 제1 정규형: 모든 속성 값은 원자 값이어야 한다.
    • 제2 정규형: 1정규형에서 부분 함수적 종속을 제거 (위의 사례처럼 기본키가 두 개 합친 걸로 되어있을 때 그걸 쪼갠 것)
    • 제3 정규형: 2정규형에서 이행적 함수적 종속을 제거
    • BCNF: 3정규형에서 모든 결정자(함수적 종속에서 왼쪽 편의 속성)를 후보키(속성의 개수를 최소로 하는 수퍼키)로 만드는 작업
    • 제4 정규형: BCNF에서 릴레이션에 다치 종속 관계(Multi Value Dependency)를 제거
    • 제5 정규형: 4정규형에서 릴레이션에 조인 종속 관계(Join Dependency)를 제거
      • 1~3 정규형까지만 한다. (속도 문제와 회사마다 4,5정규형은 달라서)
  • 정규형의 문제점
    • 조회를 할 때 빈번한 조인으로 인한 성능 저하 (정규화를 과도하게 하면 조회 속도가 느려진다.)
    • 부자연스러운 DB Semantic 초래
    • 조회/검색 위주의 응용 시스템에 부적합
  • 정규형의 문제점 해결방안
    • 정규화를 한 후 업무 특성과 성능 향상을 위해 De-normalization 수행 (역정규화하고 다시 합쳐버리기)

반 정규화

  • 시스템의 성능 향상 및 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법

방식 (요새는 이걸 많이 한다.)

  • 테이블 추가
    • 중복 테이블, 통계 테이블, 이력 테이블, 부분 테이블 추가
  • 테이블 병합
  • 테이블 분할분할 기준
    • 범위 분할: 범위 단위로 분할
    • 해시 분할: 해시 함수의 값에 따라 분할
    • 목록 분할
    • 합성 분할: 위의 방식을 조합
  • 파티셔닝(하나의 테이블의 데이터를 분리 - 행 단위로 분리하기도 하고 열 단위로 분리하기도 한다.) ex. 더 자주 사용하는 데이터들끼리 모아 놓기 (수직 분할, 수평 분할로)

테이블 생성

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 테이블이름( 컬럼이름 자료형 [컬럼 제약 조건], ,,, [테이블 제약 조건] ) 옵션

  • 자료형
  • 수치 데이터
    • BIT(N), TINYINT, SMALLINT, MEDIUMINT, INT(INTEGER, 약 21억까지 저장), BIGINT(900경), FLOAT(실수), DOUBLE(실수), DECIMAL(전체 자릿수, 소수 자릿수)
  • 문자 데이터
    • CHAR(N - 255), VARCHAR(N - 65535), BINARY(N - 255), VARBINARY(N - 255), : 상대적 짧은 거 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT : 상대적 긴 거 ENUM SET (CHAR: 크기 고정, VARCHAR: 가변, 공간을 확보하고 데이터 삽입하면 데이터의 크기로 변경 → 수정이 자주 일어나면 안 좋다.)
  • 날짜 데이터
    • DATE, DATETIME, TIMESTAMP, TIME, YEAR
  • BOOL
  • JSON
  • GEOMETRY: 공간 데이터 형식
  • 테이블 생성 시 추가하는 옵션
  • ENGINE
    • MyISAM: 조회에 유리하도록 만들 때 사용
    • InnoDB: 트랜잭션 처리에 유리하도록 만들 때 사용
      • 대부분의 경우, 특히 실무/서비스 → InnoDB 사용이 안정적이고 선호됨
  • auto_increment=초기값: 일련번호의 초기값을 설정
    • ALTER TABLE 테이블이름 auto_incremen=초기값 을 이용해서 수정 가능
  • default charset 인코딩방식: 설정하지 않으면 한글 사용 x 할 수도 있음
  • 테이블 생성

contact 테이블 num 정수, 일련번호형식, 기본키 name 영문 20자, 자주 변경 x address 영문 100자, 자주 변경됨 tel 영문 20자 email 영문 100자 자주 변경됨 birthday 날짜

조회를 자주 할 것 같고 일련번호의 첫 번째 값은 1이고 한글 사용

create table contact(
num integer auto_increment primary key,
name varchar(20),
address char(100),
tel varchar(20),
email char(100),
birthday date)ENGINE=MyISAM auto_increment=1 CHARSET=utf8;

주의!!

  • 인코딩 방식을 적지 않으면 기본적으로 데이터베이스 설치할 때의 인코딩 방식을 따른다.
  • 일반적인 문자의 경우는 utf8 로 입력이 가능한데 이모티콘을 사용하는 경우는 utf8mb4로 설정해야 한다.
create table sample1(
name varchar(20)
)ENGINE=MyISAM;
  • 이 경우 varchar의 경우 20 바이트가 아니라 utf8 20자가 들어간다 → 실제로는 60 바이트
  • 디폴트 인코딩 여부에 상관없이 utf8로 설정하고자 하는 경우에는 varchar 대신 nvarchar를 사용하면 된다.

테이블 수정

  • 컬럼 추가
    • ALTER TABLE {테이블이름} ADD {컬럼이름} 자료형 [first | after 컬럼이름]; (first: 맨 앞에 추가, after 컬럼이름: 그 컬럼 다음에 추가)
    • ex. contact 테이블에 age 컬럼 정수로 추가
    • alter table contact add age integer;
  • 테이블 구조 확인
  • DESC contact; # contact 테이블에 들어있는 컬럼들을 확인할 수 있다.
  • 컬럼 삭제
    • ALTER TABLE {테이블이름} DROP {컬럼이름};
    • ex. contact 테이블의 age 컬럼 삭제→ 기존에 데이터가 있으면 날라가버리니까 삭제 연산은 신중히!
    • alter table contact drop age;
  • 컬럼 수정
    • 이름과 자료형 변경: ALTER TABLE {테이블이름} change {이전컬럼이름} {새로운컬럼이름} 자료형;
    • 자료형만 변경: ALTER TABLE {테이블이름} modify {컬럼이름} 자료형;
      • Not NULL을 수정할 때도 자료형 변경 명령으로 수행한다. (제약 조건 수정 하는 게 아니라) ex. name char(10) not null→ 10개의 공간 가지고 o name char(10)→ 앞에 0으로 null이 아니라는 표시를 해주는 공간이 있기 때문에 11개의 공간 가지고 있다. ⇒ 메모리 공간을 바꿔주는 일이기 때문에 자료형 변경 명령이 필요하다.
    • ex. tel 컬럼의 자료형을 integer로 수정 (실제로는 tel은 문자형으로 만든다.→ 0이 앞에 오면 데이터가 없다고 받아들일 수 있기 때문에 + 전화번호 뒷자리 4자리만 확인할 때도 편리)
    • alter table contact modify tel int;
    • ex. tel 컬럼의 이름을 phone으로 수정
    • alter table contact change tel phone int;
  • 테이블 이름 수정
    • ALTER TABLE {이전테이블이름} RENAME {새로운테이블이름}
  • 테이블 삭제
    • DROP TABLE {테이블이름};
    • DROP TABLE CONTACT;
  • 테이블의 모든 데이터 삭제: 테이블 구조는 남고 데이터만 다 삭제
    • TRUNCATE TABLE {테이블이름};

제약 조건

테이블의 무결성 유지를 위해서 지켜야 하는 조건

NOT NULL

  • 필수 입력
  • 이 설정은 컬럼의 자료형에 영향을 미치므로 컬럼 제약 조건으로 설정 가능하다.

CHECK

  • 컬럼에 대입되는 값을 점검하기 위한 제약 조건
  • 컬럼 제약 조건, 테이블 제약 조건으로도 추가 가능
    • CHECK(컬럼이름 제약조건)
    • ex. gender는 남 또는 여 라는 값만 갖도록 하고 age는 1~100까지의 값을 갖도록 하는 테이블 생성- insert into sample3 values('중', 80); ⇒ 삽입 실패
      • insert into sample3 values('여', 180); ⇒ 삽입 실패
    • create table sample3( gender varchar(1) CHECK(gender='남' or '여'), # 또는 gender in ('남','여') age integer CHECK(age >= 1 and age <= 100));

UNIQUE

  • 데이터의 값이 1개만 존재하도록 해주는 제약 조건
  • 이 제약 조건이 설정된 곳에는 NULL 대입이 된다.
  • 컬럼 제약 조건, 테이블 제약 조건으로도 설정할 수 있다.
    • 이번엔 테이블 제약 조건- insert into sample4 values(1, 80); - insert into sample4 values(1, 180); ⇒ 삽입 실패 (1이 중복되었으니) - insert into sample4 values(null, 80); ⇒ null 사용 가능
      • If unique(area, popu) 라면 (1, 80), (1, 180) 삽입 성공 (동일한 area를 사용하지만 popu가 다르므로)
    • create table sample4( area int, popu int, unique(area) );

기본키는 null인지 아닌지랑 뒤에 다 확인, unique는 뒤만 확인

PRIMARY KEY

  • NOT NULL 이고 UNIQUE (없으면 안되고 중복되면 안됨)
  • 테이블에 1개만 지정 가능하다. (테이블을 생성할 때 한 번만 설정 가능하다.)
  • 여러 개의 컬럼 조합으로 기본키를 만들 때는 반드시 테이블 제약 조건으로 설정해야 한다.
create table SAMPLE6(
	CODE int,
	AREA int,
	POPU int,
	primary KEY(CODE, AREA)
);
  • primary key와 unique로 설정된 컬럼은 인덱스가 자동으로 생성돼서 조회할 때 빠르다. ⇒ 왠만하면 기본키 지정하는 것이 좋다.

FOREIGN KEY

  • 다른 테이블의 데이터를 참조하기 위해 설정하는 키
  • 현재 테이블에서는 어떤 속성이든 외래키가 될 수 있지만, 다른 테이블에서 이 컬럼은 UNIQUE나 PRIMARY KEY여야 한다.
  • 외래키 설정 방법: 2개 테이블 사이의 관계를 확인
    • 1:1 관계 - 양쪽 테이블의 기본키를 상대방 테이블의 외래키로 추가
    • 1:N 관계 - 1쪽 테이블의 기본키를 N쪽 테이블의 외래키로 추가
    • N:N 관계 - 양쪽 테이블의 기본키를 가지고 새로운 테이블을 만들고 그 테이블에서 외래키를 설정

(한 테이블의 외래키 뜻: 이 컬럼을 기본키로 가지고 있는 테이블을 참조해라!)

  • 외래키를 설정하지 않은 경우
create table tEmployee(
	name char(10) primary key,
	salary int not null,
	addr varchar(30) not null);

insert into tEmployee values('군계', 650, '제주도');
insert into tEmployee values('쥬니', 480, '수원');
insert into tEmployee values('헨리', 500, '서울');

create table tProject(
	projectid int primary key,
	employee char(10) not null,
	project varchar(30) not null,
	cost int);

insert into tProject values(1, '아이린', '수출', 800); # 없는 employee가 들어가버린다.
insert into tProject values(2, '쥬니', '요가', 300);
insert into tProject values(3, '헨리', '사이클', 200);

delete from tEmployee where name='헨리'; # 헨리가 삭제되었는데 tProject 테이블에는 남아있게 된다.
  • 외래키를 설정한 경우
create table tEmployee(
	name char(10) primary key,
	salary int not null,
	addr varchar(30) not null);

insert into tEmployee values('군계', 650, '제주도');
insert into tEmployee values('쥬니', 480, '수원');
insert into tEmployee values('헨리', 500, '서울');

# 현재 테이블의 employee 컬럼은 tEmployee 테이블의 name을 참조하도록 설정-> 이제 연결이 됨
create table tProject(
	projectid int primary key,
	employee char(10) not null,
	project varchar(30) not null,
	cost int
	foreign key(employee) references tEmployee(name));

# 참조하는 테이블에 존재하는 데이터 삭제할 수 없고 참조 당하는 테이블 제거 불가
insert into tProject values(1, '아이린', '수출', 800);
delete from tEmployee where name='헨리';
drop table tEmployee;

⇒ 참조 무결성 제약 조건: 외래키로 설정된 데이터는 참조할 수 있는 값을 가져야 한다. Null을 가질 수는 있지만 참조할 수 없는 값을 가질 수는 없다.

⇒ 외래키 옵션

  • ON DELETE {NO ACTION | CASCADE | SET NULL} 참조 당하는 데이터가 삭제될 때 동작하는 옵션으로 NO ACTION은 아무 일도 하지 않는 것이고, CASCADE를 설정하면 참조하는 테이블의 데이터도 같이 삭제되고, SET NULL을 설정하면 참조하는 데이터가 NULL로 변경된다.
  • ON UPDATE 는 수정될 때의 동작
# 현재 테이블의 employee 컬럼은 tEmployee 테이블의 name을 참조하도록 설정-> 이제 연결이 됨
create table tProject(
	projectid int primary key,
	employee char(10) not null,
	project varchar(30) not null,
	cost int
	foreign key(employee) references tEmployee(name) ON DELETE CASCADE);
728x90
반응형