[LG CNS 6기] 본 과정 23일차 TIL / [DB] - 외래키, ALTER TABLE, VIEW, DML

1. 오늘의 한 줄 요약

NOT NULL ~ 버리~지 않아~

2. 오늘 배운 것

2.1 외래키와 참조무결성

외래키(FOREIGN KEY)는 한 테이블의 컬럼이 다른 테이블의 PK를 가리키게 만드는 제약조건이다.
이 제약이 걸리면 그 컬럼엔 상대 테이블에 실제로 존재하는 값이나 NULL 말고는 들어갈 수 없다. (참조 무결성)

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

REFERENCES 부모테이블(부모컬럼)을 컬럼 옆에 바로 붙이면 FOREIGN KEY를 건 것과 같다.
부모 테이블(TB_DEPT, TB_JOB)이 먼저 존재해야 자식 테이블(TB_EMP)을 만들 수 있다.

2.2 외래키 옵션: CASCADE와 NO ACTION

부모 데이터가 삭제되거나 수정될 때 자식 데이터를 어떻게 처리할지 옵션으로 정할 수 있다.
아무 옵션도 안 주면 RESTRICT(참조하는 자식이 있으면 삭제/수정 자체를 막음)가 기본값이고, NO ACTION을 써도 MariaDB에서는 RESTRICT와 똑같이 동작한다.

FOREIGN KEY(DEPT_ID) REFERENCES TB_DEPT (DEPT_ID) ON DELETE CASCADE,
FOREIGN KEY(JOB_ID)  REFERENCES TB_JOB  (JOB_ID)  ON UPDATE CASCADE

• CASCADE: 부모가 삭제/수정되면 자식도 같이 삭제/수정된다.
• RESTRICT / NO ACTION(기본값): 참조하는 자식이 있으면 부모의 삭제/수정 자체를 막는다.
• SET NULL: 부모가 삭제/수정되면 자식의 FK 값이 NULL로 바뀐다.

CASCADE는 편해 보이지만, 부모 하나를 지웠을 뿐인데 관련된 자식 데이터가 전부 같이 사라진다는 뜻이라 사용 시 조심해야 한다.

2.3 NOT NULL은 테이블 레벨 제약이 안 된다

제약조건 대부분은 컬럼 옆에 바로 쓰거나(컬럼 레벨), 컬럼을 다 쓴 다음 따로 한 줄로 쓰는(테이블 레벨) 두 방식이 다 되는데, NOT NULL만 유일하게 테이블 레벨로는 못 쓰고 컬럼 옆에만 붙일 수 있다.

-- 이렇게 테이블 레벨 문법으로는 못 씀 (에러)
CREATE TABLE TB_EMPLOYEE(
    EMP_ID   VARCHAR(10),
    EMP_NAME VARCHAR(50),
    NOT NULL (EMP_NAME)
);

-- 컬럼 옆에 바로 붙여야 함
CREATE TABLE TB_EMPLOYEE(
    EMP_ID   VARCHAR(10),
    EMP_NAME VARCHAR(50) NOT NULL
);

나중에 ALTER로 NOT NULL을 추가할 때도 ADD CONSTRAINT가 아니라 MODIFY COLUMN을 써야 하는 이유이다.

2.4 ALTER TABLE로 제약조건 관리하기

테이블을 처음 만들 때 제약을 다 못 걸었어도, ALTER TABLE로 나중에 하나씩 추가하거나 바꿀 수 있다.

-- PK 추가
ALTER TABLE TB_EMPLOYEE
ADD CONSTRAINT PRIMARY KEY(EMP_ID);

-- NOT NULL 추가 (컬럼 레벨이라 MODIFY COLUMN)
ALTER TABLE TB_EMPLOYEE
MODIFY COLUMN DEPT_ID VARCHAR(10) NOT NULL;

-- FK 추가 (테이블 레벨이라 ADD CONSTRAINT)
ALTER TABLE TB_EMPLOYEE
ADD CONSTRAINT FOREIGN KEY (DEPT_ID) REFERENCES tb_dept(DEPT_ID);

같은 제약 추가인데도 제약 종류에 따라 MODIFY COLUMN(컬럼 속성)과 ADD CONSTRAINT(테이블 제약)로 문법이 갈린다.
컬럼 이름/타입을 함께 바꿀 땐 CHANGE COLUMN old new TYPE, PK를 다른 컬럼으로 다시 걸고 싶을 땐 DROP CONSTRAINT PRIMARY KEY 후 ADD CONSTRAINT PK_이름 PRIMARY KEY(컬럼) 두 단계가 필요했다.

2.5 제약조건이 뭐가 걸려있는지 확인하기

SHOW CREATE TABLE TB_EMPLOYEE;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM information_schema.table_constraints
WHERE table_schema = 'TABLEDB'
AND table_name = 'TB_EMPLOYEE';

SHOW CREATE TABLE은 그 테이블을 만든 CREATE 구문 전체를 다시 보여주고,
information_schema.table_constraints는 제약조건만 목록으로 뽑아준다.

2.6 VIEW

VIEW는 SELECT문에 이름을 붙여서 테이블처럼 부를 수 있게 만든 가상 테이블이다.
실제 데이터를 담고 있는 게 아니라서, 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;

복잡한 조인을 매번 새로 짜지 않고 짧게 재사용할 수 있고, 필요한 컬럼만 보여주게 만들 수도 있어서 보안 목적으로도 쓴다는 걸 배웠다.

2.7 DML: UPDATE

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

SET절에도 서브쿼리를 쓸 수 있다. 심하균의 직급/부서 값을 미리 조회해서 손으로 옮겨 적지 않고, 성해교 값을 그대로 가져와서 한 번에 바꿨다.

UPDATE employee
SET MARRIAGE = DEFAULT
WHERE EMP_NAME = '한선기';

컬럼 = DEFAULT로 쓰면 그 컬럼에 미리 정의된 기본값으로 되돌릴 수 있다.
다만 DEFAULT SYSDATE()처럼 기본값 자체가 실행 시점마다 바뀌는 컬럼도 있어서, DEFAULT라고 다 고정된 값은 아니다.

2.8 SEQUENCE와 AUTO_INCREMENT

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

SELECT NEXTVAL(SEQ_TEST);

CREATE TABLE TB_TEMP(
    ORDER_ID INT DEFAULT (NEXTVAL(SEQ_TEST)) PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(50)
);

SEQUENCE는 테이블에 딸린 게 아니라 독립된 채번 객체라서, 시작값/증가폭/최댓값을 세밀하게 정할 수 있고 여러 테이블이 하나를 같이 쓸 수도 있다.

CREATE TABLE TB_AUTO_TEMP(
    ORDER_ID INT AUTO_INCREMENT PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(50)
);

AUTO_INCREMENT는 그 컬럼 전용으로 훨씬 간단하게 1씩 증가하는 번호를 채워주지만, SEQUENCE만큼 세부 설정은 못 한다.

참고로 SEQUENCE는 MariaDB(10.3+)와 Oracle에는 있지만 MySQL에는 없는 기능이다. MySQL에서는 AUTO_INCREMENT만 쓸 수 있다.

3. 실습 / 적용

직접 해본 것:
DDL 실습 1~9번으로 TB_CATEGORY, TB_CLASS_TYPE 테이블을 만들고 다듬었다.

-- Q1~2) 제약 없이 먼저 생성
CREATE TABLE TB_CATEGORY(
    NAME     VARCHAR(10),
    USE_YN   CHAR(1) DEFAULT 'Y'
);
CREATE TABLE TB_CLASS_TYPE(
    NO   VARCHAR(5) PRIMARY KEY,
    NAME VARCHAR(10)
);

-- Q6) 컬럼명 변경
ALTER TABLE tb_category
CHANGE NAME CATEGORY_NAME VARCHAR(20);

-- Q9) 다른 테이블과 FK로 연결
ALTER TABLE TB_DEPARTMENT
ADD CONSTRAINT FK_DEPARTMENT_CATEGORY
FOREIGN KEY(CATEGORY) REFERENCES TB_CATEGORY(CATEGORY_NAME);

4. 오늘의 회고

• 느낀 점: 주말 동안 짧게라도 이번 주에 했던 내용들을 돌아봐야겠다.
• 다음에 할 것: 이번 주 내용 복습!

---

#LGCNS #LGCNS6기 #개발자 #LGCNSINSPIRECAMP