[SQL] DDL
개요
테이블과 관련 열을 생성하고 변경하고 삭제하는 명령어를 데이터 정의어(DDL, Data Definition Language)라고 한다. DDL 명령어는 따로 커밋하지 않아도 데이터베이스에 즉각 반영되며, 데이터 사전(data dictionary)에 기록된다. 신규로 테이블을 생성하는 CREATE TABLE 명령어를 제외한 나머지 DDL 명령어는 데이터베이스에 이미 정의된 테이블을 수정하거나 삭제하기 때문에 제약 조건에 의해 적용이 어려울 수 있다. 제약 조건까지 조작하려면 데이터베이스의 전체적인 설계 요소를 확인해야 한다.
DDL의 종류는 다음과 같다.
- CREATE
- ALTER
- RENAME
- DROP
- TRUNCATE
CREATE
테이블은 DB의 가장 기본적인 객체로, 행과 열의 구조로 데이터를 저장한다. CREATE는 테이블 생성을 위해 해당 테이블에 입력될 데이터를 정의하고, 정의한 데이터를 어떠한 데이터 유형으로 선언할 것인지를 결정할 때 사용한다.
사용법
1
2
3
4
5
6
CREATE TABLE 테이블명(
컬럼명1 데이터유형 [기본 값] [NOT NULL]
, 컬럼명2 데이터유형 [기본 값] [NOT NULL]
, 컬럼명3 데이터유형 [기본 값] [NOT NULL]
, ...
);
테이블 생성 시 주의해야 할 몇가지 규칙이 있다. 내용은 다음과 같다.
- 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
- 테이블명은 다른 테이블의 이름과 중복되지 않아야 한다.
- 한 테이블 내에서는 컬럼명이 중복되게 지정될 수 없다.
- 테이블 이름을 지정하고 각 컬럼들은 괄호 ‘()’로 묶어 지정한다.
- 각 컬럼들은 콤마 ‘,’로 구분되고 테이블ㄹ 생성문의 끝은 항상 세미콜론 ‘;’으로 끝난다.
- 컬럼에 대해서는 다른 테이블까지 고려해 DB 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)
- 컬럼 뒤에 데이터 유형은 꼭 지정되야 한다.
- 테이블명과 컬럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
- 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
예제
- 쿼리
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE PLAYER(
PLAYER_ID CHAR(7) NOT NULL
, PLAYER_NAME VARCHAR2(20) NOT NULL
, TEAM_ID CHAR(3) NOT NULL
, E_PLAYER_NAME VARCHAR2(40)
, NICKNAME VARCHAR2(30)
, JOIN_YYYY CHAR(4)
, POSITION VARCHAR2(10)
, BACK_NO NUMBER(2)
, NATION VARCHAR2(20)
, BIRTH_DATE DATE
, SOLAR CHAR(1)
, HEIGHT NUMBER(3)
, WEIGHT
NUMBER(3)
, CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER ID)
, CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_IN) REFERENCES TEAM (TEAM_ID)
);
- 결과
테이블 생성 예제에서 추가적인 주의 사항 몇 가지를 확인하면 다음과 같다.
- 테이블 생성시 대소문자 구분은 하지 않는다. 기본적으로 테이블이나 컬럼명은 대문자로 만들어진다.
- DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
- 컬럼과 컬럼의 구분은 콤마로 하되, 마지막 컬럼은 콤마를 찍지 않는다.
- 컬럼에 대한 제약조건이 있으면 CONSTRAINT를 이용해 추가할 수 있다.
제약조건
제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한, 즉 데이터의 무결성을 유지하기 위한 DB의 보편적인 방법으로, 테이블의 특정 컬럼에 설정하는 제약이다. 테이블을 생성할 때 제약조건을 반드시 기술할 필요는 없지만, 이후에 ALTER TABLE을 이용해 추가 및 수정하는 경우 데이터가 이미 입력된 경우라면 처리 과정이 쉽지 않으므로 초기 테이블 생성 시점부터 적합한 제약조건에 대한 충분한 검토가 있어야 한다.
제약조건의 종류의 다음과 같다.
- PRIMARY KEY(기본키)
테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다. 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며, 기본키를 구성하는 컬럼에는 NULL을 입력할 수 없다.
- UNIQUE(고유키)
테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. 단 NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약 위반이 되지 않는다.
- NOT NULL
NULL 값의 입력을 금지한다. 디폴트 상태에서는 모든 컬럼에서 NULL을 허가하고 있지만, 이 제약을 지정함으로써 해당 컬럼은 입력 필수가 된다. NOT NULL을 CHECK의 일부분으로 이해할 수도 있다.
- CHECK
입력할 수 있는 값의 범위 등을 제한한다. CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정한다.
- FOREIGN KEY(외래키)
관계형 DB에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다. 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있다.
생성된 테이블 구조 확인
테이블을 생성한 후 테이블의 구조가 제대로 만들어졌는지 확인할 필요가 있다. 오라클은 ‘DESCRIBE 테이블명;’ 또는 ‘DESC 테이블명;’ 으로 해당 테이블에 대한 정보를 확인할 수 있다.
SELECT 문장으로 테이블 생성
SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법(CTAS, Create Table ~ As Select ~)이 있다. 기존 테이블을 이용한 CTAS 방법을 사용하면 컬럼별로 데이터 유형을 다시 정의하지 않아도 되는 장점이 있다. 그러나 CTAS 기법 사용 시 주의할 점은 기존 테이블의 제약조건 중에 NOT NULL 제약만 새로 생성되는 테이블에 적용되고, 기본키·고유키·외래키·CHECK 등의 다른 제약조건은 없어진다는 점이다. 이와 같은 제약조건을 새로 생성한 테이블에 적용하기 위해서는 뒤에 나오는 ALTER TABLE 기능을 사용해야 한다.
예제 쿼리는 다음과 같다.
1
CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM;
ALTER
한번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시의 구조를 유지하게 된다. 처음의 테이블 구조를 그대로 유지하는 것이 최선이지만, 업무적인 요구 사항이나 시스템 운영상 테이블을 사용하는 도중에 변경해야 할 일들이 발생할 수도 있다. 이 경우 주로 컬럼을 추가/삭제하거나 제약조건을 추가/삭제하는 작업을 진행하게 된다.
ADD COLUMN
ADD COLUMN은 기존 테이블에 필요한 컬럼을 추가하는 명령이다.
사용법
1
2
3
4
5
6
ALTER TABLE 테이블명
ADD (
추가할 컬럼명1 데이터유형 [기본값] [NOT NULL]
[, 추가할 컬럼명2 데이터유형 [기본값] [NOT NULL]
, ...]
);
예제
예제에 사용되는 테이블 정보는 다음과 같다.
- 쿼리
1
ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));
- 결과
DROP COLUMN
DROP COLUMN은 테이블에서 필요 없는 컬럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다. 단, 컬럼 삭제 후 최소 하나 이상의 컬럼이 테이블에 존재해야 하며, 한 번 삭제된 컬럼은 복구할 수 없다.
사용법
1
2
3
4
5
6
ALTER TABLE 테이블명
DROP (
삭제할 컬럼명1
[, 삭제할 컬럼명2
, ...]
);
예제
예제에 사용되는 테이블 정보는 다음과 같다.
- 쿼리
1
ALTER TABLE PLAYER DROP (ADDRESS);
- 결과
MODIFY COLUMN
MODIFY COLUMN은 테이블에 존재하는 컬럼에 대해 ALTER TABLE 명령을 이용해 컬럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경을 포함할 수 있다.
사용법
1
2
3
4
5
6
ALTER TABLE 테이블명
MODIFY (
컬럼명1 데이터유형 [기본값] [NOT NULL]
[, 컬럼명2 데이터유형 [기본값] [NOT NULL]
, ...]
);
예제
예제에 사용되는 테이블 정보는 다음과 같다.
- 쿼리
1
ALTER TABLE PLAYER MODIFY (ADDRESS VARCHAR(120));
- 결과
RENAME COLUMN
RENAME COLUMN은 테이블을 생성하면서 만들었던 컬럼명을 어떤 이유로 불가피하게 변경해야 하는 경우에 유용하게 쓸 수 있는 구문이다. RENAME COLUMN으로 컬럼명이 변경되면, 해당 컬럼과 관계된 제약조건에 대해서도 자동으로 변경되는 장점이 있다. 하지만 ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시돼 있는 기능이 아니고 오라클 등 일부 DBMS에서만 지원한다.
사용법
1
ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼명 TO 새로운 컬럼명;
예제
예제에 사용되는 테이블 정보는 다음과 같다.
- 쿼리
1
ALTER TABLE PLAYER RENAME COLUMN ADDRESS TO ADDR;
- 결과
DROP CONSTRAINT
DROP CONSTRAINT는 테이블 생성 시 부여했던 제약조건을 삭제하는 명령어다. 예제는 생략하겠다.
사용법
1
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
ADD CONSTRAINT
ADD CONSTRAINT는 테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해 제약조건을 추가할 수 있는 명령어다. 예제는 생략하겠다.
사용법
1
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);
RENAME
RENAME은 테이블의 이름을 변경할 때 사용하는 명령어다. 예제는 생략하겠다.
사용법
1
RENAME 기존 테이블명 TO 새로운 테이블명;
DROP
DROP은 테이블을 잘못 만들었거나 테이블이 더이상 필요 없을 경우 해당 테이블을 삭제해야 한다. 다음은 불필요한 테이블을 삭제하는 명령이다. 예제는 생략하겠다.
사용법
1
DROP TABLE 테이블명 [CASCADE CONSTRAINT];
TRUNCATE
TRUNCATE는 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어 있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다. 예제는 생략하겠다.
사용법
1
TRUNCATE TABLE 테이블명;
알아두기
DROP TABLE의 경우는 테이블 자체가 없어지기 때문에 테이블 구조를 확인할 수 없다. 반면 TRUNCATE TABLE의 경우, 테이블 구조는 그대로 유지한 채 데이터만 전부 삭제하는 기능이다. TRUNCATE는 데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML로 분류할 수도 있지만 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DDL로 분류했다.
테이블에 있는 데이터를 삭제하는 명령어는 TRUNCATE TABLE 명령어 이외에도 DELETE 명령어가 있다. 그러나 DELETE와 TRUNCATE는 처리하는 방식 자체가 다르다. 테이블의 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE보다는 시스템 부하가 적은 TRUNCATE TABLE을 권고한다. 단 TRUNCATE TABLE의 경우 정상적인 복구가 불가능하므로 주의해야한다.
읽어주셔서 감사합니다. 😊
Reference
SQL 전문가 가이드 - Kdata 한국데이터산업진흥원