DDL: CREATE·ALTER·DROP

DDL은 테이블의 틀을 만들고(CREATE TABLE), 고치고(ALTER TABLE), 없앱니다(DROP TABLE). 다른 표가 참조하는 표를 없앨 때 CASCADE는 참조하는 것까지 함께 지우고, RESTRICT는 지우지 않습니다.

학생을 넣으려면 학생 표라는 틀이 먼저 있어야 합니다. 틀에는 열의 이름과 형식이 있고, 지켜야 할 규칙인 제약 조건(constraint)이 있습니다. 무결성 제약 조건에서 본 기본키·외래키·CHECK를 테이블에 적는 문장이 CREATE TABLE입니다. 쓰다가 열이 더 필요하면 ALTER TABLE로 틀을 고치고, 필요 없어진 표는 DROP TABLE로 없앱니다. 다른 표가 외래키로 가리키는 표를 없애려 하면, 가리키던 쪽을 어떻게 할지 정해야 합니다. 그것을 정하는 낱말이 CASCADE와 RESTRICT입니다.

CREATE TABLE 동아리 (  코드 CHAR(2),  이름 VARCHAR(20) NOT NULL,  회장 INT,  회비 INT DEFAULT 0,  PRIMARY KEY (코드),  FOREIGN KEY (회장)    REFERENCES 학생 (학번),  CHECK (회비 >= 0));

CREATE TABLE 동아리 (  코드 CHAR(2),  이름 VARCHAR(20) NOT NULL,  회장 INT,  회비 INT DEFAULT 0,  PRIMARY KEY (코드),  FOREIGN KEY (회장)    REFERENCES 학생 (학번),  CHECK (회비 >= 0));

1CREATE TABLE — 열의 형식과 제약 조건

CREATE TABLE 동아리 (가 동아리라는 빈 테이블의 틀을 만듭니다. 괄호 안의 열 줄마다 열의 이름과 형식이 붙습니다. INT는 정수, CHAR(2)는 길이가 2로 정해진 문자열, VARCHAR(20)은 길이가 20까지 바뀌는 문자열입니다. NOT NULL은 비울 수 없다는 규칙, DEFAULT 0은 값을 적지 않으면 0을 넣으라는 규칙입니다. 제약 줄이 열에 꼬리표를 답니다. PRIMARY KEY는 기본키, FOREIGN KEY와 REFERENCES는 학생 표의 학번을 가리키는 외래키, CHECK는 값이 지켜야 할 조건입니다. 제약 조건은 열 옆에 쓰거나(이름의 NOT NULL), 열을 다 쓴 뒤 따로 씁니다(PRIMARY KEY부터 CHECK까지). 회비를 적지 않은 행을 넣으면 회비 칸에 기본값 0이 들어갑니다. 행을 넣는 INSERT는 DDL이 아니라 DML입니다.

2ALTER TABLE — 열을 더하고 지우기

열을 더할 때는 ADD, 지울 때는 DROP COLUMN을 씁니다. ALTER TABLE 학과 ADD 전화 …⁠가 학과 표에 열을 더합니다. 새 열은 맨 뒤에 붙고, 이미 있던 행의 그 칸은 NULL입니다. ALTER TABLE 학과 DROP COLUMN 건물⁠이 건물 열을 그 안의 값과 함께 지웁니다.

3DROP TABLE — RESTRICT와 CASCADE

학생 표의 학과코드가 외래키로 학과를 가리킵니다. 그래서 DROP TABLE 학과 RESTRICT⁠는 학과를 지우지 않습니다. DROP TABLE 학과 CASCADE⁠는 학과를 지우면서, 학과를 가리키던 학생 표의 외래키 제약 조건도 함께 지웁니다. 학생의 행은 그대로 남습니다. 두 낱말은 ALTER TABLE의 DROP COLUMN 뒤에도 같은 뜻으로 붙습니다.

4헷갈리는 두 CASCADE

무결성 제약 조건의 ON DELETE CASCADE도 CASCADE(연쇄)라는 낱말을 씁니다. 둘은 언제, 무엇을 함께 지우는지가 다릅니다.

예제

시험 문제의 SQL은 대개 표준 SQL 꼴입니다. 아래 예제는 표준 꼴로 쓰고 Oracle에서 돌렸습니다. Oracle이 받지 않는 꼴은 따로 밝힙니다.

CREATE TABLE

CREATE TABLE 동아리 (
  코드 CHAR(2),
  이름 VARCHAR(20)
    NOT NULL,
  회장 INT,
  회비 INT DEFAULT 0,
  PRIMARY KEY (코드),
  FOREIGN KEY (회장)
    REFERENCES 학생 (학번),
  CHECK (회비 >= 0)
);
INSERT INTO 동아리
  (코드, 이름, 회장)
VALUES ('AI', '인공지능', 101);
SELECT *
FROM 동아리;

ALTER TABLE

ALTER TABLE 학과
  ADD 전화 VARCHAR(20);
ALTER TABLE 학과
  DROP COLUMN 건물;
SELECT *
FROM 학과
ORDER BY 학과코드;

DROP TABLE의 RESTRICT와 CASCADE

시험 꼴은 다음과 같습니다.

DROP TABLE 학과 RESTRICT;
DROP TABLE 학과 CASCADE;

Oracle은 RESTRICT와 CASCADE를 이 꼴로 받지 않습니다. 아무것도 붙이지 않으면 RESTRICT처럼 막습니다. 학생 표가 학과를 가리키므로 DROP이 ORA-02449 오류를 내고, 학과는 3행 그대로 남습니다.

DROP TABLE 학과;
SELECT COUNT(*) AS 학과수
FROM 학과;

CASCADE는 CASCADE CONSTRAINTS로 씁니다. 학과가 지워지면서 학생 표의 외래키 제약 조건도 사라집니다. 그래서 학과에 없던 'ME'를 학과코드로 넣어도 막히지 않습니다.

DROP TABLE 학과
  CASCADE CONSTRAINTS;
INSERT INTO 학생
VALUES (108, '윤하은',
    1, 'ME');
SELECT 학번,
  학과코드
FROM 학생
WHERE 학번 >= 107
ORDER BY 학번;

학생 행은 하나도 지워지지 않았습니다. CASCADE가 함께 지우는 것은 학과를 가리키던 제약 조건이지, 학생의 행이 아닙니다. 표준 SQL의 CASCADE는 지우는 표로 만든 뷰도 함께 지웁니다. 뷰는 뷰에서 봅니다.

시험 포인트

• 필기: CASCADE는 지우는 것을 가리키는 제약 조건·뷰까지 함께 지우고, RESTRICT는 가리키는 것이 있으면 지우지 않습니다. ALTER TABLE은 ADD로 열을 더하고 DROP COLUMN으로 열을 지웁니다. 더한 열은 맨 뒤에 붙고 이미 있던 행의 그 칸은 NULL입니다.
• 실기: 조건을 보고 CREATE TABLE 문을 씁니다 — 열마다 이름과 형식, 그리고 NOT NULL·DEFAULT·PRIMARY KEY·FOREIGN KEY … REFERENCES·CHECK. DROP 문의 빈칸에는 CASCADE나 RESTRICT를 씁니다.

확인 문제

1. 수강 표의 과목코드가 과목 표의 과목코드를 가리킵니다. 시험 꼴로 DROP TABLE 과목 CASCADE;⁠를 실행한 결과로 알맞은 것을 고르세요.

• ① 과목 표가 지워지고, 수강 표도 함께 지워진다
• ② 과목 표가 지워지고, 수강 표의 OS·NW 행이 함께 지워진다
• ③ 과목 표가 지워지고, 수강 표의 외래키 제약 조건이 사라진다
• ④ 수강 표가 가리키고 있어 과목 표가 지워지지 않는다

2. 장학 표를 다루는 (가)~(라)의 일에 쓰는 문장을 키워드만 차례로 쓰세요. 표 이름과 열 이름은 빼고 키워드만 씁니다. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 장학 표를 처음 만듭니다.
• (나) 만든 장학 표에 비고 열을 하나 붙입니다.
• (다) 붙인 비고 열을 다시 없앱니다.
• (라) 장학 표를 없앱니다. 가리키는 것이 하나라도 있으면 없애지 않는 꼴과, 가리키는 제약 조건·뷰까지 함께 없애는 꼴을 시험 꼴로 둘 다 쓰세요.

3. 상담 기록을 담을 상담 표를 만듭니다. (1)~(3)을 차례로 풀어 보세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

(1) 다음 틀의 ①~③에 들어갈 낱말을 쓰세요. 괄호는 빈칸 표시입니다.

CREATE TABLE 상담 (
  상담번호 INT,
  학번 INT,
  내용 VARCHAR(100) ( ① ),
  회차 INT ( ② ) 1,
  PRIMARY KEY (상담번호),
  ( ③ ) (학번)
    REFERENCES 학생 (학번)
);

• ① 내용을 비워 둔 채로는 넣지 못하게 합니다.
• ② 회차를 적지 않고 넣으면 1이 들어가게 합니다.
• ③ 학번에 학생 표의 학번에 있는 값만 받게 합니다.

(2) 만든 상담 표에 상담일 열(VARCHAR(10))을 붙이는 문장을 쓰세요.

(3) 상담 표를 없애면서, 상담 표를 가리키는 제약 조건과 뷰까지 함께 없애는 문장을 시험 꼴로 쓰세요.

CREATE TABLE 상담 (
  상담번호 INT,
  학번 INT,
  내용 VARCHAR(100)
    NOT NULL,
  회차 INT DEFAULT 1,
  PRIMARY KEY (상담번호),
  FOREIGN KEY (학번)
    REFERENCES 학생 (학번)
);
ALTER TABLE 상담
  ADD 상담일 VARCHAR(10);
INSERT INTO 상담
  (상담번호, 학번, 내용)
VALUES (1, 103, '진로');
SELECT *
FROM 상담;

DROP TABLE 상담 CASCADE;

더 자세히: SQLD의 DDL