[LG CNS AM 6기] 23일차 TIL : DDL·VIEW·DML 정리

윤현일·2026년 8월 28일
K-DTLGCNSLGCNS6기LGCNS6기TILView내일배움카드

LGCNS

목록 보기
29/43

1. 오늘의 한 줄 요약

외래키로 테이블 사이의 관계를 정의하고, VIEW와 DML을 이용해 데이터를 안전하고 편리하게 조회·수정하는 방법을 학습했다.

오늘은 어제 배운 DDL과 제약조건을 실제 부모·자식 테이블 관계에 적용했다. 이후 ALTER TABLE로 제약조건을 추가하고, VIEW, DML, SEQUENCE, AUTO_INCREMENT까지 실습했다.

2. 배운 내용

핵심 개념

외래키와 참조 무결성

외래키(Foreign Key)는 자식 테이블의 컬럼이 부모 테이블의 기본키 또는 고유키를 참조하도록 만드는 제약조건이다.

이번 실습에서는 다음과 같은 관계를 만들었다.

TB_DEPT  ──┐
           ├── TB_EMP
TB_JOB   ──┘
  • TB_DEPT: 부서 정보
  • TB_JOB: 직급 정보
  • TB_EMP: 사원 정보
  • TB_EMP.DEPT_ID는 TB_DEPT.DEPT_ID를 참조한다.
  • TB_EMP.JOB_ID는 TB_JOB.JOB_ID를 참조한다.

부모 테이블에 존재하지 않는 부서나 직급은 사원 테이블에 저장할 수 없다.

CREATE TABLE TB_EMP (
    EMP_ID    VARCHAR(10) PRIMARY KEY,
    EMP_NAME  VARCHAR(50) NOT NULL,
    HIRE_DATE DATE DEFAULT (CURRENT_DATE),
    DEPT_ID   VARCHAR(10) NOT NULL,
    JOB_ID    VARCHAR(10) NOT NULL,

    CONSTRAINT FK_EMP_DEPT
        FOREIGN KEY (DEPT_ID)
        REFERENCES TB_DEPT (DEPT_ID)
        ON DELETE CASCADE,

    CONSTRAINT FK_EMP_JOB
        FOREIGN KEY (JOB_ID)
        REFERENCES TB_JOB (JOB_ID)
        ON UPDATE CASCADE
);

외래키가 참조할 부모 테이블을 먼저 만들고, 그다음 자식 테이블을 만들어야 한다.

ON DELETE와 ON UPDATE

부모 데이터가 삭제되거나 기본키가 변경될 때 자식 데이터를 어떻게 처리할지 외래키 옵션으로 정할 수 있다.

옵션동작
CASCADE부모의 변경이나 삭제를 자식에게 전파한다.
RESTRICT연결된 자식이 존재하면 부모의 변경이나 삭제를 막는다.
NO ACTIONMariaDB에서는 일반적으로 RESTRICT와 같은 방식으로 처리된다.
SET NULL자식의 외래키 값을 NULL로 변경한다.

ON DELETE CASCADE

DELETE FROM TB_DEPT
WHERE DEPT_ID = '10';

TB_EMP.DEPT_ID에 ON DELETE CASCADE를 설정했다면 10번 부서가 삭제될 때 해당 부서 소속 사원도 함께 삭제된다.

편리하지만 부모 행 하나를 삭제했을 때 여러 자식 데이터가 함께 삭제될 수 있으므로 주의해야 한다.

ON UPDATE CASCADE

UPDATE TB_JOB
SET JOB_ID = 'J10'
WHERE JOB_ID = 'J1';

ON UPDATE CASCADE가 설정돼 있다면 부모의 직급 ID가 변경될 때 이를 참조하던 사원의 JOB_ID도 자동으로 변경된다.

DEFAULT와 NULL의 차이

컬럼에 기본값을 지정하면 값을 생략하거나 DEFAULT를 작성했을 때 기본값이 들어간다.

INSERT INTO TB_EMP (
    EMP_ID,
    EMP_NAME,
    HIRE_DATE,
    DEPT_ID,
    JOB_ID
)
VALUES (
    '200',
    'JSLIM',
    DEFAULT,
    '10',
    'J1'
);

하지만 명시적으로 NULL을 입력하면 기본값이 아니라 NULL이 저장된다. 단, 해당 컬럼이 NULL을 허용해야 한다.

INSERT INTO TB_EMP (
    EMP_ID,
    EMP_NAME,
    HIRE_DATE,
    DEPT_ID,
    JOB_ID
)
VALUES (
    '300',
    'JSLIM',
    NULL,
    '10',
    'J1'
);

정리하면 다음과 같다.

  • 컬럼 생략: 기본값 사용
  • DEFAULT: 기본값 사용
  • NULL: NULL 저장 또는 NOT NULL 오류 발생

ALTER TABLE로 제약조건 관리하기

기존 테이블에도 ALTER TABLE을 사용하여 컬럼과 제약조건을 추가할 수 있다.

ALTER TABLE TB_EMPLOYEE
ADD CONSTRAINT PK_TB_EMPLOYEE
PRIMARY KEY (EMP_ID);

외래키도 나중에 추가할 수 있다.

ALTER TABLE TB_EMPLOYEE
ADD CONSTRAINT FK_EMPLOYEE_JOB
FOREIGN KEY (JOB_ID)
REFERENCES TB_JOB (JOB_ID);
ALTER TABLE TB_EMPLOYEE
ADD CONSTRAINT FK_EMPLOYEE_DEPT
FOREIGN KEY (DEPT_ID)
REFERENCES TB_DEPT (DEPT_ID);

외래키를 추가할 때는 기존 데이터도 함께 검사한다. 이미 부모 테이블에 존재하지 않는 값이 저장돼 있다면 외래키 추가가 실패한다.

NOT NULL은 테이블 제약조건이 아니라 컬럼의 속성이므로 MODIFY COLUMN을 사용해야 한다.

ALTER TABLE TB_EMPLOYEE
MODIFY COLUMN DEPT_ID VARCHAR(10) NOT NULL;

기본키를 삭제할 때 MariaDB에서는 다음 문법을 사용한다.

ALTER TABLE TB_CATEGORY
DROP PRIMARY KEY;

제약조건을 다시 추가할 때는 이름을 지정하는 것이 관리하기 편하다.

ALTER TABLE TB_CATEGORY
ADD CONSTRAINT PK_CATEGORY_NAME
PRIMARY KEY (CATEGORY_NAME);

제약조건 확인하기

SHOW INDEX는 기본키와 인덱스 정보를 확인할 때 사용할 수 있지만, 모든 제약조건을 보여주지는 않는다.

테이블에 설정된 전체 제약조건은 INFORMATION_SCHEMA에서 확인할 수 있다.

SELECT CONSTRAINT_NAME,
       CONSTRAINT_TYPE,
       TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'TABLEDB'
  AND TABLE_NAME = 'TB_EMPLOYEE';

INFORMATION_SCHEMA는 DBMS가 데이터베이스, 테이블, 컬럼, 제약조건 등의 메타데이터를 관리하는 시스템 영역이다.

VIEW

뷰(View)는 실제 데이터를 별도로 저장하는 테이블이 아니라, SELECT 문장을 이름으로 저장한 논리적인 테이블이다.

CREATE OR REPLACE VIEW EMP_VIEW (
    NAME,
    DEPT
)
AS
SELECT E.EMP_NAME,
       D.DEPT_NAME
FROM DEPARTMENT D
JOIN EMPLOYEE E
  ON D.DEPT_ID = E.DEPT_ID
WHERE E.DEPT_ID = '90';

이후에는 복잡한 조인 쿼리를 반복해서 작성하지 않고 뷰를 조회할 수 있다.

SELECT *
FROM EMP_VIEW;

뷰의 장점은 다음과 같다.

  • 복잡한 조인이나 서브쿼리를 감출 수 있다.
  • 자주 사용하는 쿼리를 재사용할 수 있다.
  • 사용자에게 필요한 컬럼만 공개할 수 있다.
  • 실제 테이블 구조가 아닌 업무 중심의 조회 구조를 제공할 수 있다.

단순 뷰와 복합 뷰

단일 테이블의 컬럼을 그대로 조회하는 단순 뷰는 조건에 따라 수정이 가능하다.

CREATE OR REPLACE VIEW VW_학생일반정보 (
    학번,
    학생이름,
    주소
)
AS
SELECT STUDENT_NO,
       STUDENT_NAME,
       STUDENT_ADDRESS
FROM TB_STUDENT;

뷰를 수정하면 실제로는 기반 테이블의 데이터가 수정된다.

UPDATE VW_학생일반정보
SET 학생이름 = '정황엽'
WHERE 학번 = 'A213046';

반면 다음 요소가 포함된 복합 뷰는 일반적으로 수정할 수 없다.

  • GROUP BY
  • 집계 함수
  • DISTINCT
  • UNION
  • 여러 테이블의 외부 조인
  • 계산된 컬럼

따라서 뷰는 기본적으로 조회용으로 생각하고, 수정 가능 여부를 확인한 뒤 사용하는 것이 안전하다.

WITH CHECK OPTION

수정 가능한 뷰에 WITH CHECK OPTION을 지정하면 뷰의 WHERE 조건을 벗어나는 데이터를 뷰를 통해 입력하거나 수정할 수 없다.

CREATE OR REPLACE VIEW VW_휴학생
AS
SELECT STUDENT_NO,
       STUDENT_NAME,
       ABSENCE_YN
FROM TB_STUDENT
WHERE ABSENCE_YN = 'Y'
WITH CHECK OPTION;

다음 쿼리는 해당 학생을 휴학생 뷰의 조건에서 벗어나게 만들기 때문에 거부된다.

UPDATE VW_휴학생
SET ABSENCE_YN = 'N'
WHERE STUDENT_NO = 'A213046';

WITH CHECK OPTION은 뷰를 통한 수정 결과가 뷰의 목적과 조건을 계속 만족하도록 보호한다.

DML

DML(Data Manipulation Language)은 테이블에 저장된 데이터를 추가·수정·삭제하는 언어다.

  • INSERT: 데이터 추가
  • UPDATE: 데이터 수정
  • DELETE: 데이터 삭제

UPDATE와 서브쿼리

심하균 사원의 부서와 직급을 성해교 사원과 동일하게 변경했다.

UPDATE EMPLOYEE
SET DEPT_ID = (
        SELECT E.DEPT_ID
        FROM EMPLOYEE E
        WHERE E.EMP_NAME = '성해교'
    ),
    JOB_ID = (
        SELECT E.JOB_ID
        FROM EMPLOYEE E
        WHERE E.EMP_NAME = '성해교'
    )
WHERE EMP_NAME = '심하균';

같은 작업을 UPDATE JOIN으로도 작성할 수 있다.

UPDATE EMPLOYEE T
JOIN EMPLOYEE S
  ON S.EMP_NAME = '성해교'
SET T.DEPT_ID = S.DEPT_ID,
    T.JOB_ID = S.JOB_ID
WHERE T.EMP_NAME = '심하균';

해외영업2팀 사원의 보너스 비율을 수정할 때도 서브쿼리를 활용했다.

UPDATE EMPLOYEE
SET BONUS_PCT = 0.3
WHERE DEPT_ID = (
    SELECT DEPT_ID
    FROM DEPARTMENT
    WHERE DEPT_NAME = '해외영업2팀'
);

UPDATE나 DELETE를 실행하기 전에는 같은 WHERE 조건으로 SELECT를 먼저 실행하여 대상 행을 확인하는 습관이 중요하다.

SEQUENCE

시퀀스는 연속된 숫자를 생성하는 독립적인 데이터베이스 객체다.

CREATE SEQUENCE SEQ_TEST
START WITH 1000
INCREMENT BY 2
MAXVALUE 1005;

다음 값을 가져올 때는 NEXTVAL()을 사용할 수 있다.

SELECT NEXTVAL(SEQ_TEST);

테이블의 기본값으로 시퀀스를 연결할 수도 있다.

CREATE TABLE TB_TEMP (
    ORDER_ID INT DEFAULT (NEXTVAL(SEQ_TEST)) PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(50)
);
INSERT INTO TB_TEMP (CUSTOMER_NAME)
VALUES ('JSLIM');

ORDER_ID를 직접 입력하지 않아도 시퀀스의 다음 값이 저장된다.

AUTO_INCREMENT

MariaDB에서는 AUTO_INCREMENT를 사용해 테이블별 자동 증가 컬럼을 만들 수도 있다.

CREATE TABLE TB_AUTO_TEMP (
    ORDER_ID INT AUTO_INCREMENT PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(50)
);
구분SEQUENCEAUTO_INCREMENT
형태독립된 데이터베이스 객체테이블 컬럼의 속성
사용 범위여러 곳에서 호출 가능해당 테이블에서 사용
설정시작값, 증가값 등을 세밀하게 지정비교적 간단하게 사용
값 생성명시적으로 다음 값을 요청행 추가 시 자동 생성

둘 다 삭제나 롤백 등의 상황에서 번호가 건너뛸 수 있으므로, 반드시 빈틈없는 연속 번호가 필요한 업무 데이터로 생각하면 안 된다.

새롭게 알게 된 점

  • 부모 테이블의 변경을 자식 테이블에 자동으로 전파할 수 있다.
  • 외래키를 나중에 추가하면 기존 데이터까지 제약조건 검사를 받는다.
  • DEFAULT를 사용하는 것과 명시적으로 NULL을 넣는 것은 다르다.
  • 뷰에서 데이터를 수정하면 기반 테이블의 실제 데이터가 변경될 수 있다.
  • WITH CHECK OPTION은 뷰의 조건을 벗어나는 수정을 막는다.
  • 시퀀스는 테이블과 독립적으로 숫자를 생성하는 객체다.

헷갈렸던 점

VIEW를 새로운 테이블이라고 생각하기 쉬웠지만, 뷰는 일반적으로 데이터를 별도로 저장하지 않고 조회 쿼리를 저장한다.

또한 뷰에서 UPDATE가 실행됐을 때 뷰만 바뀌는 것이 아니라 기반 테이블의 데이터가 수정된다는 점도 주의해야 한다.

3. 실습 / 적용

직접 해본 것

카테고리와 과목 구분 테이블 변경하기

CREATE TABLE TB_CATEGORY (
    CATEGORY_NAME VARCHAR(20),
    USE_YN CHAR(1) DEFAULT 'Y',

    CONSTRAINT PK_CATEGORY_NAME
        PRIMARY KEY (CATEGORY_NAME)
);
CREATE TABLE TB_CLASS_TYPE (
    CLASS_TYPE_NO VARCHAR(10),
    CLASS_TYPE_NAME VARCHAR(20) NOT NULL,

    CONSTRAINT PK_CLASS_TYPE_NO
        PRIMARY KEY (CLASS_TYPE_NO)
);

컬럼 이름과 자료형을 변경하고 기본키를 다시 설정하면서 ALTER TABLE의 동작을 확인했다.

학과와 카테고리 연결하기

ALTER TABLE TB_DEPARTMENT
ADD CONSTRAINT FK_DEPARTMENT_CATEGORY
FOREIGN KEY (CATEGORY)
REFERENCES TB_CATEGORY (CATEGORY_NAME);

외래키를 추가하기 전에 TB_DEPARTMENT.CATEGORY의 기존 값과 TB_CATEGORY.CATEGORY_NAME이 모두 일치하는지 확인해야 했다.

지도 면담 뷰 만들기

지도교수가 배정되지 않은 학생도 조회하기 위해 교수 테이블에는 LEFT JOIN을 사용했다.

CREATE OR REPLACE VIEW VW_지도면담 (
    학생이름,
    학과이름,
    지도교수이름
)
AS
SELECT S.STUDENT_NAME,
       D.DEPARTMENT_NAME,
       P.PROFESSOR_NAME
FROM TB_STUDENT S
JOIN TB_DEPARTMENT D
  ON S.DEPARTMENT_NO = D.DEPARTMENT_NO
LEFT JOIN TB_PROFESSOR P
  ON S.COACH_PROFESSOR_NO = P.PROFESSOR_NO;

학과별 학생 수 뷰 만들기

학생이 없는 학과도 포함할 수 있도록 학과를 기준으로 LEFT JOIN했다.

CREATE OR REPLACE VIEW VW_학과별학생수 (
    DEPARTMENT_NAME,
    STUDENT_COUNT
)
AS
SELECT D.DEPARTMENT_NAME,
       COUNT(S.STUDENT_NO)
FROM TB_DEPARTMENT D
LEFT JOIN TB_STUDENT S
  ON S.DEPARTMENT_NO = D.DEPARTMENT_NO
GROUP BY D.DEPARTMENT_NO,
         D.DEPARTMENT_NAME;

최근 3년 동안 수강생이 많았던 과목 조회하기

수업 조건에 따라 최근 3년을 2007년부터 2009년까지로 설정하고, 누적 수강생 수가 많은 과목 3개를 조회했다.

SELECT C.CLASS_NO AS 과목번호,
       C.CLASS_NAME AS 과목이름,
       COUNT(*) AS `누적수강생수(명)`
FROM TB_CLASS C
JOIN TB_GRADE G
  ON C.CLASS_NO = G.CLASS_NO
WHERE SUBSTRING(G.TERM_NO, 1, 4)
      BETWEEN '2007' AND '2009'
GROUP BY C.CLASS_NO,
         C.CLASS_NAME
ORDER BY COUNT(*) DESC,
         C.CLASS_NO
LIMIT 3;

결과

테이블 생성 후 제약조건을 추가하고, 실제 데이터를 입력·수정·삭제하면서 데이터베이스가 관계와 규칙을 검사하는 과정을 확인했다.

또한 반복해서 사용하는 조인과 집계 쿼리를 뷰로 만들어 조회 코드를 단순화할 수 있었다.

4. 문제와 해결

막힌 부분

외래키를 추가할 때 문법만 올바르면 바로 적용될 것으로 생각했다. 하지만 부모 테이블에 없는 값을 자식 테이블이 이미 가지고 있으면 제약조건 추가가 실패했다.

또한 VW_학생일반정보의 컬럼 별칭을 학생이름으로 만들고 UPDATE에서는 이름을 사용하면 존재하지 않는 컬럼이라는 오류가 발생한다.

해결 방법

외래키를 추가하기 전에 부모 테이블에 존재하지 않는 값을 먼저 확인했다.

SELECT E.DEPT_ID
FROM TB_EMPLOYEE E
LEFT JOIN TB_DEPT D
  ON E.DEPT_ID = D.DEPT_ID
WHERE D.DEPT_ID IS NULL;

조회 결과가 있다면 데이터를 먼저 수정하거나 부모 데이터를 추가한 뒤 외래키를 설정해야 한다.

뷰에서는 생성할 때 지정한 컬럼 별칭을 정확히 사용했다.

UPDATE VW_학생일반정보
SET 학생이름 = '정황엽'
WHERE 학번 = 'A213046';

5. 다음에 할 일

  • COMMIT과 ROLLBACK으로 DML 변경 취소하기
  • ON DELETE CASCADE, RESTRICT, SET NULL 동작 비교하기
  • 수정 가능한 뷰와 수정할 수 없는 뷰를 직접 만들어 보기
  • WITH CHECK OPTION이 없는 뷰와 있는 뷰 비교하기
  • SEQUENCE와 AUTO_INCREMENT에서 번호가 건너뛰는 상황 확인하기

공식 문서: MariaDB Foreign Keys, CREATE VIEW, 뷰를 통한 데이터 수정, Sequence Overview, AUTO_INCREMENT

profile
윤현일
개발자
이전 포스트

[LG CNS AM 6기] 21일차 TIL : SQL GROUP BY와 HAVING, JOIN

다음 포스트

[LG CNS AM 6기] 25일차 TIL : Spring MVC 요청 매핑과 DTO 자동 바인딩, Swagger API 문서화, MyBatis Mapper로 DB 연결하기

0개의 댓글