DDL
IT 위키
더 많은 작업
- 상위 문서: SQL
- Data Definition Language
데이터베이스 구조를 정의 및 수정하기 위해 사용되는 언어
CREATE TABLE 부서 (
부서번호 NUMBER(2) PRIMARY KEY,
부서명 VARCHAR2(20) NOT NULL
);
CREATE TABLE 사원 (
사원번호 NUMBER(4) CONSTRAINT 사원_PK PRIMARY KEY,
이름 VARCHAR2(20) NOT NULL,
급여 NUMBER(7) DEFAULT 0 CONSTRAINT 사원_CK CHECK (급여 >= 0),
부서번호 NUMBER(2) CONSTRAINT 사원_FK REFERENCES 부서(부서번호) ON DELETE CASCADE
);
- 따옴표로 감싸지 않은 이름은 문자로 시작해야 한다.
CREATE TABLE 1D (...)는 ORA-00903 오류가 난다. - 영문자와 숫자, 밑줄(_), 달러($), 샵(#)만 쓸 수 있다. Oracle은 $와 #은 쓰지 말라고 권한다.
- 예약어는 쓸 수 없다.
CREATE TABLE SELECT (...)는 오류가 나지만D_SELECT처럼 예약어를 포함한 이름은 된다. - 길이는 COMPATIBLE 초기화 매개변수가 12.2 이상이면 1~128바이트, 12.2 미만이면 1~30바이트이다. 글자 수가 아니라 바이트 기준이라 AL32UTF8에서 한글 이름은 3바이트씩 차지한다. 실습(23ai)에서 한글 42자(126바이트)는 만들어졌고 43자(129바이트)는 ORA-00972 오류가 났다.
- 한 스키마 안에서 테이블 이름은 중복될 수 없고, 한 테이블 안에서 칼럼 이름도 중복될 수 없다.
- 큰따옴표로 감싼 이름은 이 규칙의 예외지만 대소문자를 구분하므로 시험과 실무에서는 잘 쓰지 않는다.
| Oracle | SQL Server | 설명 |
|---|---|---|
| CHAR(n) | CHAR(n) | 고정 길이 문자. 정의한 길이보다 짧은 값은 뒤를 공백으로 채운다. |
| VARCHAR2(n) | VARCHAR(n) | 가변 길이 문자. 입력한 길이만큼만 저장한다. |
| NUMBER(p, s) | NUMERIC(p, s), DECIMAL(p, s), INT 등 | 숫자. p는 전체 자릿수, s는 소수점 이하 자릿수이다. |
| DATE | DATETIME | 날짜와 시각. Oracle DATE는 초 단위까지 저장한다. |
| TIMESTAMP | DATETIME2 | 소수점 이하 초까지 저장하는 날짜·시각 |
- NUMBER(5,2) 칼럼에 123.456을 넣으면 123.46으로 반올림되어 저장되고, 1234.5를 넣으면 정수부가 3자리를 넘어 ORA-01438 오류가 난다.
- CHAR와 VARCHAR2는 비교 방식이 다르다. 양쪽이 모두 CHAR(문자 리터럴 포함)이면 짧은 쪽에 공백을 채워 비교하고, 한쪽이라도 VARCHAR2이면 공백을 채우지 않고 그대로 비교한다.
CREATE TABLE 문자 (C CHAR(5), V VARCHAR2(5));
INSERT INTO 문자 VALUES ('AB', 'AB');
SELECT LENGTH(C), LENGTH(V),
CASE WHEN C = 'AB' THEN 'Y' ELSE 'N' END AS C비교,
CASE WHEN V = 'AB ' THEN 'Y' ELSE 'N' END AS V비교,
CASE WHEN C = V THEN 'Y' ELSE 'N' END AS CV비교
FROM 문자;
| LENGTH(C) | LENGTH(V) | C비교 | V비교 | CV비교 |
|---|---|---|---|---|
| 5 | 2 | Y | N | N |
- C에는 'AB '(5자리)가 저장된다. C = V는 'AB '과 'AB'를 공백 없이 비교하므로 거짓이다.
| 제약조건 | 설명 |
|---|---|
| PRIMARY KEY | 행을 유일하게 식별한다. UNIQUE와 NOT NULL을 합친 것과 같으며 테이블당 하나만 만들 수 있다. 여러 칼럼을 묶은 복합 키도 하나의 기본 키이다. |
| UNIQUE | 값이 중복되지 않아야 한다. NULL은 허용한다. Oracle에서는 한 칼럼 UNIQUE에 NULL을 여러 개 넣을 수 있다. |
| NOT NULL | NULL을 허용하지 않는다. 칼럼 정의에만 쓸 수 있다. |
| CHECK | 조건식을 만족하는 값만 허용한다. 예: CHECK (급여 >= 0)
|
| FOREIGN KEY | 다른 테이블(부모)의 기본 키나 UNIQUE 키를 참조한다. 부모에 없는 값은 넣을 수 없다. NULL은 허용한다. |
| DEFAULT | 엄밀히는 제약조건이 아니라 칼럼 속성이다. 값을 주지 않았을 때 들어갈 기본값을 정한다. |
위 사원 테이블에서 실행한 결과는 다음과 같다.
| 문장 | 결과 |
|---|---|
INSERT INTO 사원 (사원번호, 이름, 부서번호) VALUES (3, '박', 20) |
성공, 급여에 DEFAULT 0이 들어간다. |
INSERT INTO 사원 VALUES (4, '최', -1, 10) |
ORA-02290 CHECK 제약 위반 |
INSERT INTO 사원 VALUES (4, '최', 100, 30) |
ORA-02291 부모 키 없음(부서 30이 없음) |
- 제약조건 정보는
USER_CONSTRAINTS에서 볼 수 있다. CONSTRAINT_TYPE은 P(기본 키), U(UNIQUE), R(외래 키), C(CHECK와 NOT NULL)이다. - 이름을 주지 않으면 Oracle이 SYS_C로 시작하는 이름을 붙인다.
부모 행을 지울 때 그 값을 참조하는 자식 행을 어떻게 할지 정한다. 데이터베이스 참조 무결성을 유지하기 위한 규칙이다.
| 동작 | 부모 행 삭제 시 | Oracle 문법 |
|---|---|---|
| 제한(RESTRICT, NO ACTION) | 자식 행이 있으면 삭제를 막는다. | ON DELETE 절 생략(기본값). ORA-02292 오류 |
| CASCADE | 자식 행도 함께 삭제한다. | ON DELETE CASCADE |
| SET NULL | 자식의 외래 키 값을 NULL로 바꾼다. | ON DELETE SET NULL |
| SET DEFAULT | 자식의 외래 키 값을 기본값으로 바꾼다. | Oracle은 지원하지 않는다(SQL Server는 지원). |
- Oracle은 ON DELETE만 지원하고 ON UPDATE 절은 없다. SQL Server는 ON DELETE와 ON UPDATE 모두에 NO ACTION, CASCADE, SET NULL, SET DEFAULT를 쓸 수 있다.
ON DELETE CASCADE 실행 예. 부서는 (10, 영업), (20, 개발)이다.
| 사원번호 | 이름 | 급여 | 부서번호 |
|---|---|---|---|
| 1 | 김 | 300 | 10 |
| 2 | 이 | 400 | 20 |
| 3 | 박 | 0 | 20 |
DELETE FROM 부서 WHERE 부서번호 = 20; -- 1행 삭제
SELECT * FROM 사원 ORDER BY 사원번호;
| 사원번호 | 이름 | 급여 | 부서번호 |
|---|---|---|---|
| 1 | 김 | 300 | 10 |
- 부서 20을 지우자 그 부서를 참조하던 사원 2, 3번이 함께 삭제되었다.
- ON DELETE SET NULL로 만든 자식 테이블에서는 같은 삭제 후 자식 행이 남고 부서번호만 NULL이 되었다.
- ON DELETE 절 없이 만든 자식 테이블에 참조 행이 있으면 부모 삭제는 ORA-02292(자식 레코드 발견) 오류가 났다.
서브쿼리 결과로 테이블을 만들면서 데이터도 복사한다. 구조만 복사하려면 거짓 조건을 준다.
CREATE TABLE 사원복사 AS SELECT * FROM 사원; -- 구조 + 데이터
CREATE TABLE 사원빈복사 AS SELECT * FROM 사원 WHERE 1 = 2; -- 구조만
원본과 복사본의 제약조건을 USER_CONSTRAINTS로 비교한 결과는 다음과 같다.
| 테이블 | CONSTRAINT_TYPE | 조건 |
|---|---|---|
| 사원 | C | "이름" IS NOT NULL |
| 사원 | C | 급여 >= 0 |
| 사원 | P | |
| 사원 | R | |
| 사원복사 | C | "이름" IS NOT NULL |
- 복사본에는 명시적으로 만든 NOT NULL 제약만 넘어온다. 기본 키, UNIQUE, 외래 키, CHECK 제약과 인덱스, DEFAULT 값은 복사되지 않는다.
- 기본 키 때문에 암묵적으로 생긴 NOT NULL도 넘어오지 않는다. 위 결과에서 사원번호에는 NOT NULL이 없다.
- SQL Server에서는
SELECT * INTO 사원복사 FROM 사원형태를 쓴다.
| 작업 | Oracle | SQL Server |
|---|---|---|
| 칼럼 추가 | ALTER TABLE 사원 ADD (입사일 DATE); |
ALTER TABLE 사원 ADD 입사일 DATE;
|
| 칼럼 변경 | ALTER TABLE 사원 MODIFY (이름 VARCHAR2(40)); |
ALTER TABLE 사원 ALTER COLUMN 이름 VARCHAR(40);
|
| 칼럼 삭제 | ALTER TABLE 사원 DROP COLUMN 입사일; |
ALTER TABLE 사원 DROP COLUMN 입사일;
|
| 칼럼 이름 변경 | ALTER TABLE 사원 RENAME COLUMN 급여 TO 연봉; |
EXEC sp_rename '사원.급여', '연봉', 'COLUMN';
|
| 제약 추가 | ALTER TABLE 사원 ADD CONSTRAINT 사원_PK PRIMARY KEY (사원번호); |
같은 문법 |
| 제약 삭제 | ALTER TABLE 사원 DROP CONSTRAINT 사원_PK; |
같은 문법 |
| 테이블 이름 변경 | RENAME 사원 TO 사원백업; 또는 ALTER TABLE 사원 RENAME TO 사원백업; |
EXEC sp_rename '사원', '사원백업';
|
- Oracle은 칼럼 하나를 추가·변경할 때 괄호를 생략할 수 있고, 여러 칼럼을 한 번에 다룰 때는 괄호 안에 쉼표로 나열한다.
- 칼럼 길이를 줄이거나 타입을 바꾸면 이미 들어 있는 데이터와 맞지 않을 때 오류가 난다.
DROP TABLE 부서; -- ORA-02449: 다른 테이블의 외래 키가 참조 중
DROP TABLE 부서 CASCADE CONSTRAINTS; -- 참조하는 외래 키 제약을 함께 지우고 삭제
TRUNCATE TABLE 사원복사; -- 구조는 남기고 모든 행 삭제
- CASCADE CONSTRAINTS로 부서를 지운 뒤 사원 테이블에는 사원_FK가 사라지고 사원_PK, 사원_CK, NOT NULL 제약만 남았다. 사원 행은 지워지지 않는다.
- TRUNCATE는 DDL이라 Oracle에서는 롤백할 수 없다. DELETE, TRUNCATE, DROP의 차이는 DML 문서의 비교 표를 참고한다.
- 기본 키는 UNIQUE + NOT NULL이고 테이블당 하나이다. UNIQUE는 NULL을 허용한다.
- CTAS로 복사하면 NOT NULL 제약만 넘어오고 기본 키·외래 키·CHECK·DEFAULT는 넘어오지 않는다.
- Oracle의 칼럼 변경은 MODIFY, SQL Server는 ALTER COLUMN이다. 칼럼 이름 변경은 Oracle RENAME COLUMN, SQL Server sp_rename이다.
- ON DELETE CASCADE와 SET NULL을 적용한 뒤 자식 테이블의 남은 행 수를 묻는 문제가 나온다. ON DELETE를 생략하면 자식이 있는 부모 행은 지울 수 없다.
- 테이블명은 문자로 시작해야 하고, 예약어는 쓸 수 없으며, A-Z, a-z, 0-9, _, $, #만 쓸 수 있다.