[오라클/ORACLE] 데이터 정의어(DDL) - CREATE, ALTER, DROP

데이터 정의어(DDL)

데이터베이스의 객체를 생성(CREATE), 수정(ALTER), 삭제(DROP)하는 명령어를 데이터 정의어(Data Definition Language)라고 한다. 객체란 데이터를 저장할 수 있는 공간을 의미하는데 데이터베이스에서는 테이블, 인덱스 등을 일컫는다.

• CREATE TABLE
• ALTER TABLE
• DROP TABLE

CREATE TABLE

의미 그대로 테이블을 만드는 명령문이다. 테이블을 구성하고 속성을 정한다. 속성에 관한 제약사항(PRIMARY KEH, FORIEGN KEY 등)도 정의한다.

사용방법은 이렇다. []에 들어간 값은 생략 가능하다는 의미다.

CREATE TABLE 테이블 이름 
( 
    속성이름1 데이터 타입 [NULL|NOT NULL|UNIQUE|DEFAULT 기본값|CHECK 체크조건],
    속성이름2 데이터 타입 [NULL|NOT NULL|UNIQUE|DEFAULT 기본값|CHECK 체크조건],
    속성이름3 데이터 타입 [NULL|NOT NULL|UNIQUE|DEFAULT 기본값|CHECK 체크조건],
    ...
    [PRIMARY KEY 속성이름]
    [FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
    [ON DELETE {CASCADE|SET NULL}]
    -- CASCADE >> 참조하고 있는 값 모두 삭제 SET NULL >> 참조하고 있는 값을 NULL로 바꿈
);

생략 가능한 값이 많아서 좀 복잡해 보일 수도 있지만, 오히려 실제로 쓰이는 코드를 보면 훨씬 단순하다.

아래는 테이블을 생성할 때 속성에 부여하는 데이터 타입의 종류들을 정리한 표다.

데이터 타입	설명
NUMBER(전체 길이, 소수점 이하 자리수)	숫자 저장(음수, 양수).
VARCHAR2(SIZE)	가변길이 문자열 저장.최대 크기 4000byte
CHAR(SIZE)	고정길이 문자형 저장. 최대 크기2000byte
DATE	날짜, 연도, 월, 일, 시간 등 저장
LONG	가변길이 문자열 저장. 최대 크기 2GB
BLOB/CLOB	파일을 저장할 때 사용. BLOB - byte 배열로 저장 CLOB - char배열로 저장
ROWRID	데이터베이스에서 각 데이터의 주소

NUMBER과 VARCHAR2가 가장 빈번하게 사용된다. 참고로 VARCHAR2 대신 VARCHAR를 사용할 수도 있다. 현재 둘의 기능상 차이는 없다. 그렇지만 오라클 측에서 VARCHAR를 다른 용도로 활용할 가능성이 있기 때문에 애당초 VARCHAR2로 프로그래밍 하는 것이 추천된다. 이는 오라클의 오피셜이다.

VARCHAR2에서 가변길이 문자열을 저장한다는 의미는 저장된 데이터에 맞춰 크기를 자동 조절한다는 말이다. 예를 들어 VARCHAR2의 최대 크기는 4000byte지만 받은 크기가 2000byte면 그에 맞게 데이터 낭비 없이 할당한다.

예제를 살펴보자. persons이라는 테이블명으로 person_id, first_name, last_name이라는 컬럼을 만들었다.

CREATE TABLE persons(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);

persons 테이블은 이렇게 구성돼 있다.

DESC persons;

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50)

 

persons 테이블

현재로선 값이 저장되지 않은 테이블이다.

테이블 복사

번외로 테이블을 복사하는 방법도 알아본다. CREATE TABLE을 하는 명령문에서 조금만 추가해주면 간단히 구현할 수 있다.

CREATE TABLE 복사로 만들 테이블명 AS SELECT 가져올 컬럼 FROM 복사할 테이블명;

위의 예제로 사용한 테이블 persons를 복사해서 persons2를 만드는 코드는 아래와 같다.

CREATE TABLE persons2 AS SELECT * FROM persons;
-- persons 테이블에 있는 모든 레코드를 복사함
CREATE TABLE persons2 AS SELECT first_name FROM persons;
-- persons 테이블에 있는 first_name 컬럼만 복사

테이블에 있는 데이터는 그대로 두고 컬럼을 이루는 형태를 가져오는 방법으로도 활용된다. 방법은 조건을 붙이고 FALSE를 반환하도록 하는 것이다.

CREATE TABLE persons AS SELECT * FROM persons WHERE 1 = 0;

ALTER TABLE

ALTER TABLE은 이미 존재하는 테이블의 값을 수정할 때 사용된다.

ALTER TABLE 테이블이름
[ADD 속성이름 데이터타입]
[DROP COLUMN 속성이름]
[ALTER COLUMN 속성이름 데이터타입]
[ALTER COLUMN 속성이름 [NULL|NOT NULL]]
[ADD PRIMARY KEY(속성이름)]
[[ADD|DROP|제약이름]]
;

[]로 표시된 내용이 수정 가능한 것들이다.

• ADD : 컬럼 추가
• DROP: 컬럼 삭제
• ALTER COLUMN: 컬럼 수정
• ADD PRIMARY KEY: 기본키 설정
• ADD|DROP|제약이름: 제약사항을 삭제

위에서 사용한 persons 테이블로 ALTER TABLE을 실습해보자.

ALTER TABLE persons ADD birthdate DATE NOT NULL;

 

birthdate 컬럼이 추가됐다.

DESC persons;

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50) 
BIRTHDATE  NOT NULL DATE

 

ALTER TABLE persons MODIFY birthdate DATE NULL;

birthdate 컬럼의 값을 NULL로 수정했다.

DESC persons

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE           DATE 

DROP TABLE

DROP TABLE은 테이블을 데이터베이스에서 완전히 삭제하는 명령어다.

DROP TABLE 테이블명;

으로 간단하게 삭제된다. 컴퓨터와 비슷하게 이렇게 삭제를 하면 오라클의 휴지통으로 가게 된다. 휴지통으로 간 테이블은 다시 복구할 수 있다.

DROP TABLE 테이블명 PURGE;

반면 테이블명 뒤에 PURGE를 붙이면 테이블이 휴지통에 보존되지 않고 완전히 삭제된다.

만약 참조를 통해 두 개 이상의 테이블이 관계를 맺은 경우 DROP TABLE을 순서에 맞게 사용해야 한다. 자식 테이블(참조한 테이블) 먼저 삭제 후 부모 테이블 삭제해야 에러가 발생하지 않는다.