[DATABASE] DDL 이란? CREATE TABLE, ALTER TABLE, RENAME TABLE, DROP TABLE, TRUNCATE TABLE

Database/개념

[DATABASE] DDL 이란? CREATE TABLE, ALTER TABLE, RENAME TABLE, DROP TABLE, TRUNCATE TABLE

꽁담 2018. 10. 28. 02:13

DDL 이란?

DATA DEFINITION LANGUAGE 로 데이터 정의 언어를 의미합니다.

데이터 유형

데이터베이스의 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준을 의미합니다.

테이블의 칼럼이 가지고 있는 대표적인 4가지 데이터 유형은 다음과 같습니다.

1. CHARACTER : 고정 길이 문자열 정보

2. VARCHAR : 가변길이 문자열 정보

3. NUMERIC : 정수, 실수 등 숫자 정보

4. DATE : 날짜와 시각 정보

이 외에도 Binary String Type, Binary Large Object String Type, National Character String Type, Boolean Type 등의 유형이 있습니다.

벤더사별로 표준을 따르지만 이외에 대해서는 특성에 맞추므로 타입이나 쿼리가 다를 수 있습니다.

CREATE TABLE

테이블은 일정한 형식에 의해서 생성됩니다.

생성을 위해서는 해당 테이블에 입력될 데이터를 정의하고, 어떠한 유형으로 선언할 것인지를 결정해야 합니다.

데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 컬럼이나 후보키 중에 하나를 선정하여 기본키 컬럼으로 지정합니다.

기본키는 단일 컬럼이 아닌 여러개의 컬럼으로도 만들 수 있습니다.

테이블 생성 시에 주의해야 할 규칙

1. 객체를 의미할 수 있는 적절한 이름을 사용

2. 다른 테이블의 이름과 중복되지 않도록 지정

3. 한 테이블 내에서는 컬럼명이 중복되게 지정할 수 없음

4. 테이블 이름을 지정하고 각 컬럼들은 괄호로 묶어서 지정

5. 각 컬럼들은 , 로 구분되고, 테이블 생성문의 끝은 ; 기호로 맺음

6. 컬럼뒤에 데이터 유형은 꼭 지정되어야 함

7. 테이블 명과 컬럼 명은 반드시 문자로 시작

8. 벤더사마다 사전에 정의한 예약어는 사용 불가

9, A-Z, a-z, 0-9, _, $, # 문자만 허용

컬럼에 대한 규칙

1. DATETIME 유형에는 별도로 크기를 지정하지 않음

2. 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시

3. 컬럼의 구분은 , 로 하되 마지막 컬럼은 콤마를 찍지 않음

4. 컬럼에 대해서 CONSTRAINT 를 이용하여 제약조건을 추가 가능

테이블에 대한 생성 예제

테이블 명 : MOZI
컬럼 명   : ID (아이디) 문자 가변 자릿수 7자리
            NAME (이름) 문자 가변 자릿수 20자리
            AGE (나이)  숫자 3자리
제약조건  : 기본키(PRIMARY KEY) -> ID
            값이 반드시 존재 (NOT NULL) -> NAME

CREATE TABLE MOZI (
  ID    VARCHAR(7),
  NAME  CHAR(20) NOT NULL,
  AGE   NUMBER(3),
  CONSTRAINT PLAYER_PK PRIMARY KEY (ID) );

SELECT 문장을 통한 테이블 생성

CTAS (CREATE TABLE AS SELECT) 를 통하여 테이블을 생성할 수 있습니다.

그러나 이 때에는 제약조건 중 NOT NULL 만 복제 테이블에 적용이 되고, 나머지 제약조건을 복사되지 않습니다.

제약조건

사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한 제약사항입니다.

제약조건의 종류

구분	설명
PRIMARY KEY	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에는 하나의 기본키 제약만 정의할 수 있다. 정의 시 자동으로 UNIQUE 인덱스를 생성하며, NULL 값이 올 수 없다.
UNIQUE KEY	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. 단, NULL 은 고유키 제약의 대상이 아니므로 NULL 행은 여러개가 될 수 있다.
NOT NULL	NULL 값의 입력을 금지한다.
CHECK	입력할 수 있는 값의 범위 등을 제한한다.
FOREIGN KEY	테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우를 의미한다.
DEFAULT	DEFAULT 값을 정의시 컬럼에 데이터값이 입력되지 않은 경우 정의된 값이 자동으로 입력된다.

ALTER TABLE

처음의 테이블 구조를 그대로 유지하는 것이 최선이지만, 변경해야 할 일들이 발생할 수도 있습니다.

주로 칼럼을 추가/삭제하거나 제약조건을 추가/삭제하는 작업을 진행합니다.

컬럼 추가

새롭게 추가된 컬럼은 테이블의 마지막 컬럼이 되며 위치를 지정할 순 없습니다.

ALTER TABLE <TABLE_NAME> ADD (<COLUMN_NAME> <COLUMN_TYPE>) ; -- ORACLE, GOLDILOCKS
ALTER TABLE <TABLE_NAME> ADD <COLUMN_NAME> <COLUMN_TYPE> ; -- MS-SQL, GOLDILOCKS
ALTER TABLE <TABLE_NAME> ADD COLUMN (<COLUMN_NAME> <COLUMN_TYPE>); -- GOLDILOCKS

컬럼 제거

데이터가 있거나 없거나 삭제가 가능합니다.

한 번에 하나의 컬럼만 삭제가 가능하며, 컬럼 삭제 후 최소 하나 이상의 컬럼이 테이블에 존재해야 합니다.

한 번 삭제한 컬럼은 복구가 불가능 하므로 주의해야 합니다.

ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME>; -- ORACLE, MS-SQL
ALTER TABLE <TABLE_NAME> SET UNUSED COLUMN <COLUMN_NAME>; -- GOLDILOCKS

컬럼 수정

컬럼의 데이터 유형, 디폴트, NOT NULL 제약조건 등에 대한 변경을 할 수 있습니다.

컬럼을 변경할 때는 다음을 고려해야 합니다.

1. 컬럼의 크기를 늘릴 수는 있지만 줄이지는 못한다.

2. 해당 컬럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 컬럼의 폭을 줄일 수 있다.

3. 해당 컬럼이 NULL 값만 가지고 있으면 데이터 유형을 변경할 수 있다.

4. 해당 컬럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

5. 해당 컬럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

ALTER TABLE <TABLE_NAME> MODIFY (<COLUMN_NAME> <COLUMN_TYPE> [DEFAULT] [NOT NULL] [, .. ]); -- ORACLE
ALTER TABLE <TABLE_NAME> ALTER (<COLUMN_NAME> <COLUMN_TYPE> [DEFAULT] [NOT NULL] [, .. ]); -- MS-SQL
ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> SET DATA TYPE <COLUMN_TYPE>; -- GOLDILOCKS
ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> SET DEFAULT <DEFAULT>; -- GOLDILOCKS
ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> SET NOT NULL; -- GOLDILOCKS

컬럼 명 변경

ALTER TABLE <TABLE_NAME> RENAME COLUMN <COLUMN_NAME> TO <NEW_COLUMN_NAME>; -- ORACLE, GOLDILOCKS
sp_rename <COLUMN_NAME>, <NEW_COLUMN_NAME>, 'COLUMN'; --MS-SQL

제약조건 삭제

ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <CONSTRAINT_NAME>; -- ORACLE, MS-SQL, GOLDILOCKS

제약조건 추가

ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> <CONSTRAINT_TYPE> (<COLUMN_NAME>); -- ORACLE, MS-SQL, GOLDILOCKS

RENAME TABLE

테이블 명을 변경합니다.

RENAME <TABLE_NAME> TO <NEW_TABLE_NAME>; -- ORACLE
sp_rename <TABLE_NAME>, <NEW_TABLE_NAME>; -- MS-SQL
ALTER TABLE <TABLE_NAME> RENAME TO <NEW_TABLE_NAME>; -- GOLDILOCCKS

DROP TABLE

테이블을 잘못 만들었거나 테이블이 더 이상 필요 없을 경우 해당 테이블을 삭제합니다.

DROP TABLE <TABLE_NAME>; -- ORACLE, MS-SQL, GOLDILOCKS

TRUNCATE TABLE

테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있단 모든 행들이 제거됩니다.

또한 저장 공간을 재사용 가능하도록 해제합니다.

테이블의 데이터를 일괄 삭제하는 명령어로 DML 로 분류할 수도 있지만 내부 처리 방식이나 Auto Commit 특성으로 인해 DDL 로 분류됩니다.

TRUNCATE TABLE <TABLE_NAME>: -- ORACLE, MS-SQL, GOLDILOCKS

이 내용은 'SQL 전문가 가이드, 한국데이터베이스 진흥원' 에서 공부한 내용입니다.

저작자표시 비영리 변경금지