DDL

DDL(Data Definition Language, 데이터 정의어)은 테이블의 구조를 만들고(CREATE), 바꾸고(ALTER), 지웁니다(DROP·TRUNCATE). 테이블을 만들 때 제약 조건(constraint)으로 들어올 값의 규칙도 정합니다.

행을 넣기 전에 틀부터 만들고, 틀에 규칙을 제약 조건으로 적어 두면 어기는 행은 DBMS가 막습니다. 그림의 PROJ는 새로 만드는 프로젝트 테이블입니다.

INSERT INTO PROJVALUES (3, '서울 행사',    500, 10);INSERT INTO PROJVALUES (4, '서울 행사',    500, 50);

CREATE TABLE PROJ (  PNO NUMBER(4) PRIMARY KEY,  …);

1CREATE TABLE과 제약 조건

열 이름 뒤에 데이터 타입과 제약 조건을 씁니다. NUMBER(4)는 4자리까지의 숫자, VARCHAR2(20)은 20바이트까지의 문자열입니다.

CHECK는 거짓일 때만 막고, UNIQUE 열의 NULL은 Oracle은 여럿, SQL Server는 하나만 받습니다. 제약 조건은 열 옆(열 수준)이나 열을 다 쓴 뒤(테이블 수준)에 쓰고, 두 열을 합친 기본키는 테이블 수준으로만 씁니다. 이미 있는 테이블에는 ALTER TABLE의 ADD CONSTRAINT 이름으로 더합니다.

자식 행이 가리키는 부모 행은 지울 수 없습니다(ORA-02292). 외래키에 ON DELETE CASCADE를 붙이면 자식 행도 지워지고, ON DELETE SET NULL이면 자식의 외래키 칸만 NULL이 됩니다. 이처럼 부모 행이 없는 외래키 값이 남거나 들어오지 않게 하는 방법을 참조 동작(referential action)이라 합니다. 부모 행을 지우거나 키를 바꿀 때(Delete·Modify Action)와 자식 행을 넣을 때(Insert Action)로 나눠 이름을 붙입니다.

Oracle은 ON DELETE CASCADE·ON DELETE SET NULL만 적을 수 있고, 아무것도 적지 않은 기본이 NO ACTION이라 자식이 있는 부모 행은 지우지 못합니다(ORA-02292). SET DEFAULT는 ORA-03001, RESTRICT·NO ACTION을 글로 적거나 ON UPDATE를 쓰면 ORA-02000 오류입니다. SQL Server는 ON DELETE·ON UPDATE에 NO ACTION(기본)·CASCADE·SET NULL·SET DEFAULT를 받습니다. 넣을 때는 두 DBMS 모두 고르는 문법이 없고, 부모에 없는 값은 막습니다(ORA-02291).

CREATE TABLE … AS SELECT⁠로 만든 테이블에는 제약 조건 가운데 직접 건 NOT NULL만 복사되고, 기본키에 딸린 NOT NULL은 빠집니다.

2ALTER TABLE

ADD로 더한 열에 DEFAULT 0처럼 기본값을 주면, 이미 있던 행에도 NULL 대신 0이 들어갑니다. MODIFY로 기본값만 바꾸면 이미 있던 행은 그대로이고, 뒤에 넣는 행부터 새 기본값이 들어갑니다. MODIFY는 있는 값과 어긋나게 못 바꿔서, NULL이 있는 열에 NOT NULL을 걸면 ORA-02296, 값보다 짧게 줄이면 ORA-01441, 값이 든 열의 데이터 타입(숫자 → 문자 등)을 바꾸면 ORA-01439입니다.

SQL Server의 ALTER COLUMN은 열 하나씩 바꾸고, NOT NULL을 다시 적지 않으면 NULL 허용이 됩니다. Oracle의 MODIFY는 여러 열을 한 번에 바꿀 수 있고, 있던 NOT NULL은 그대로 둡니다.

Oracle의 이름 바꾸기와 제약 조건 더하기·지우기는 이렇게 씁니다.

ALTER TABLE PROJ
  RENAME COLUMN PNAME TO NAME;
RENAME PROJ TO PROJECT;
ALTER TABLE PROJECT
  ADD CONSTRAINT PROJ_CK
    CHECK (BUDGET <= 9000);
ALTER TABLE PROJECT
  DROP CONSTRAINT PROJ_CK;

3TRUNCATE와 DROP

다른 테이블의 외래키가 가리키는 부모 테이블은 TRUNCATE(ORA-02266)도 DROP(ORA-02449)도 막히고, DROP은 CASCADE CONSTRAINTS를 붙이면 그 외래키까지 지웁니다.

DROP TABLE DEPT
  CASCADE CONSTRAINTS;

Oracle은 PURGE 없이 DROP한 테이블을 휴지통(recycle bin)에 두어, 공간은 휴지통을 비울 때 돌려주고 ROLLBACK 대신 FLASHBACK으로 되살립니다.

FLASHBACK TABLE PROJ
  TO BEFORE DROP;

예제

예제는 저마다 처음 데이터에서 시작합니다. 그림의 PROJ를 만들어 세 행을 넣습니다.

CREATE TABLE PROJ (
  PNO NUMBER(4) PRIMARY KEY,
  PNAME VARCHAR2(20)
    NOT NULL UNIQUE,
  BUDGET NUMBER(5)
    CHECK (BUDGET > 0),
  DEPTNO NUMBER(2)
    REFERENCES DEPT (DEPTNO)
);
INSERT INTO PROJ
VALUES (1, '앱 개편',
    3000, 20);
INSERT INTO PROJ
VALUES (2, '채용 박람회',
    800, 40);
INSERT INTO PROJ
VALUES (3, '부산 지점',
    1500, 30);
SELECT COUNT(*) AS CNT
FROM PROJ;

ON DELETE CASCADE를 붙이고 40번 부서를 지웁니다.

CREATE TABLE PROJ (
  PNO NUMBER(4) PRIMARY KEY,
  DEPTNO NUMBER(2)
    REFERENCES DEPT (DEPTNO)
    ON DELETE CASCADE
);
INSERT INTO PROJ
VALUES (1, 20);
INSERT INTO PROJ
VALUES (2, 40);
INSERT INTO PROJ
VALUES (3, 30);
DELETE FROM DEPT
WHERE DEPTNO = 40;
SELECT PNO,
  DEPTNO
FROM PROJ
ORDER BY PNO;

40번을 가리키던 2번 프로젝트도 지워졌습니다. EMP를 DELETE로 비우면 ROLLBACK으로 6행이 돌아오고, TRUNCATE로 비우면 돌아오지 않습니다.

DELETE FROM EMP;
ROLLBACK;
SELECT COUNT(*) AS CNT
FROM EMP;

TRUNCATE TABLE EMP;
ROLLBACK;
SELECT COUNT(*) AS CNT
FROM EMP;

시험 포인트

• 기본키는 UNIQUE와 NOT NULL을 합친 것이고 테이블에 하나입니다. CHECK·UNIQUE·외래키는 NULL을 막지 않습니다.
• 참조 동작의 뜻을 바꿔 냅니다. RESTRICT(지울 때)는 자식이 없을 때만 지우고, DEPENDENT(넣을 때)는 부모가 있을 때만 넣고, AUTOMATIC(넣을 때)은 없는 부모를 만들고 넣습니다.
• CREATE TABLE … AS SELECT⁠는 제약 조건 가운데 직접 건 NOT NULL만 복사합니다.
• 열 바꾸기는 Oracle이 MODIFY, SQL Server가 ALTER COLUMN입니다.
• TRUNCATE·DROP은 ROLLBACK이 안 되고, 다른 테이블의 외래키가 가리키는 부모 테이블에는 둘 다 막힙니다.

확인 문제

1. 예제의 PROJ(행 3개)에 하나씩 따로 넣을 때 오류가 나지 않는 행을 모두 고르세요.

• ① (4, '서울 행사', NULL, NULL)
• ② (NULL, '서울 행사', 500, 10)
• ③ (4, '앱 개편', 500, 10)
• ④ (5, '물류 개선', 900, 40)

2. SELECT의 결과는 무엇일까요? BONUS와 GRADE가 다른 까닭도 말해 보세요.

CREATE TABLE EMP10 AS
SELECT EMPNO,
  ENAME
FROM EMP
WHERE DEPTNO = 10;
ALTER TABLE EMP10
  ADD (BONUS NUMBER(3));
ALTER TABLE EMP10
  ADD (GRADE NUMBER(1)
    DEFAULT 1);
INSERT INTO EMP10
  (EMPNO, ENAME)
VALUES (1009, '한유진');
SELECT *
FROM EMP10
ORDER BY EMPNO;

3. 예제의 ON DELETE CASCADE 블록에서 40번 대신 20번 부서를 지우면 PROJ는 어떻게 될까요? 까닭도 말해 보세요.

CREATE TABLE PROJ (
  PNO NUMBER(4) PRIMARY KEY,
  DEPTNO NUMBER(2)
    REFERENCES DEPT (DEPTNO)
    ON DELETE CASCADE
);
INSERT INTO PROJ
VALUES (1, 20);
INSERT INTO PROJ
VALUES (2, 40);
INSERT INTO PROJ
VALUES (3, 30);
DELETE FROM DEPT
WHERE DEPTNO = 20;
SELECT PNO,
  DEPTNO
FROM PROJ
ORDER BY PNO;