DDL

CREATE TABLE

테이블은 데이터베이스의 가장 기본적인 객체로, 행과 열의 구조로 데이터를 저장한다. 테이블 생성을 위해서는 해당 테이블에 입력될 데이터를 정의하고, 정의한 데이터를 어떠한 데이터 유형으로 선언할 것인지를 결정해야 한다.

가. 테이블과 칼럼 정의
테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정해 기본키 칼럼으로 지정한다.

테이블과 테이블 간의 정의된 관계는 기본키와 외부키를 활용해 설정하도록 한다.

나. CREATE TABLE

테이블을 생성하는 구문 형식은 다음과 같다.

CREATE TABLE 테이블명(
   칼럼명1 데이터유형 [기본 값] [NOT NULL]
 , 칼럼명2 데이터유형 [기본 값] [NOT NULL]
 , 칼럼명3 데이터유형 [기본 값] [NOT NULL]
 , ...
 )

create table HI(   
   deptno     number(2,0) PRIMARY KEY 
 , dname      varchar2(14) NOT NULL
 , loc        varchar2(13)
);

테이블 생성 시에 주의해야 할 몇 가지 규칙이 있다.

테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고.
테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
테이블 이름을 지정하고 각 칼럼들은 괄호'()'로 묶어 지정한다.
각 컬럼들은 콤마 ','로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ';'으로 끝난다.
칼럼에 대해서는 다른 테이블까지 고려해 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다(데이터 표준화 관점).
칼럼 뒤에 데이터 유형은 꼭 지정돼야 한다.
테이블명과 칼럼명은 반드시 문자로 시작해야 한고, 벤더별로 길이에 대한 한계가 있다.
벤더에서 사전에 정의한 예약어는 쓸 수 없다.
A-Z,a-z,0-9, _, $, # 문자만 허용된다.
테이블 생성시 대소문자 구분은 하지 않는다. 기본적으로 대문자로 만들어진다.
DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.
칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용해 추가할 수 있다.

테이블 생성문에서 제약 조건은 각 칼럼의 데이터 유형 뒤에 기술하는 칼럼 LEVEL 정의 방식과, 테이블 정의 마지막 모든 제약조건을 기술하는 테이블 LEVEL 정의 방식이 있다. 하나의 SQL 문장 내에서 두 가지 방식은 혼용해서 사용할 수 있으며, 같은 기능을 갖고 있다.

한 테이블 안에서 컬럼 이름은 달라야 하지만, 다른 테이블의 칼럼 이름과는 같을 수 있다.

다. 제약조건

사용자가 원하는 조건의 데이터만 유지하기 위한, 즉 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로, 테이블의 특정 칼럼에 설정하는 제약이다.
테이블을 생성할 때 제약조건을 반드시 기술할 필요는 없지만, 이후에 ALTER TABLE을 이용해 추가-수정하는 경우 데이터가 이미 입력된 경우라면 처리 과정이 쉽지 않으므로 초기 테이블 생성 시점부터 적합한 제약조건에 대한 충분한 검토가있어야 한다.

제약조건의 종류

• PRIMARY KEY(기본키) : 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다.
  기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며, 기본키를 구성하는 컬럼에는 NULL을 입력할 수 없다.
• UNIQUE(고유키) : 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. 단 NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약 위반이 되지 않는다.
• NOT NULL : NULL 값의 입력을 금지한다. 디폴트 상태에서는 모든 칼럼에서 NULL을 허가하고 있지만, 이 제약을 지정함으로써 해당 칼럼은 입력 필수가 된다. NOT NULL을 CHECK의 일부분으로 이해할 수도 있다.
• CHECK : 입력할 수 있는 값의 범위 등을 제한한다. CHECK 제약으로 TRUE or FALSE로 평가할 수 있는 논리식을 지정한다.
• FOREIGN KEY(외래키) : 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다. 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있다.

NULL의 의미
NULL(ASCII 00)으로 공백(32)이나 숫자 0(48)과는 전혀 다른 값이며, 조건에 맞는 데이터가 없을 때의 공집합과도 다르다. 'NULL'은 '아직 정의되지 않은 미지의 값'이거나 '아직 데이터가 입력되지 않은 경우'를 의미한다.

DEFAULT 의미
데이터 입력 시 칼럼 값을 지정하지 않은 경우 NULL 값이 입력된다. 하지만 사전에 DEFAULT 값을 정의했다면, NULL 값 대신 사전에 정의된 기본 값(DEFAULT)이 자동으로 입력된다.

create table dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
)

라. 생성된 테이블 구조 확인

테이블 생성한 후 테이블의 구조가 제대로 만들어졌느닞 확인할 필요가 있다. Oracle에서는 'DESCRIBE 테이블명;' 또는 간략히 'DESC 테이블명;'으로 해당 테이블에 대한 정보를 확인할 수 있다.

DESC DEPT;

마. SELECT 문장으로 테이블 생성 사례

SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법(CTAS, Create Table ~ AS Select ~)이 있다. 기존 테이블을 이용한 CTAS 방법을 사용하면 칼럼별로 데이터 유형을 다시 정의하지 않아도 되는 장점이 있다.
그러나 CTAS 기법 사용 시 주의할 점은 기존 테이블의 제약조건 중에 NOT NULL 제약만 새로 생성되는 테이블에 적용되고, 기본키 고유키 외래키 CHECK 등의 다른 제약조건은 없어진다는 점이다. 이와 같은 제약조건을 새로 생성한 테이블에 적용하기 위해서는 뒤에 나오는 ALTER TABLE 기능을 사용해야 한다.

Create Table DE AS
Select * FROM DEPT;

Select * FROM DE;

DESC DE;

ALTER TABLE

한 번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시의 구조를 유지하게 된다. 처음의 테이블 구조를 그대로 유지하는 것이 최선이지만, 업무적인 요구 사항이나 시스템 운영상 테이블을 사용하는 도중에 변경해야 할 일들이 발생할 수도 있다. 이 경우 주로 칼럼을 추가/삭제하거나 제약조건을 추가/삭제하는 작업을 진행하게 된다.

가. ADD COLUMN
다음은 기존 테이블에 필요한 칼럼을 추가하는 명령이다.

ALTER TABLE 테이블명
	ADD( 추가할 칼럼명1 데이터유형 [기본 값] [NOT NULL]
      [, 추가할 칼럼명2 데이터유형 [기본 값] [NOT NULL]
       , ...]);

ALTER TABLE DE
ADD (HI VARCHAR(1));

DESC DE;

주의할 것은 새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치를 지정할 수는 없다.

나. DROP COLUMN
테이블에서 필요 없는 칼럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다. 단 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 하며, 한 번 삭제된 칼럼은 복구할 수 없다.

ALTER TABLE 테이블명 DROP(삭제할 칼럼명1 [, 삭제할 칼럼명2, ... ]);

ALTER TABLE DE DROP(HI);

DESC DE;

다. MODIFY COLUMN
테이블에 존재하는 칼럼에 대해 ALTER TABLE 명령을 이용해 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대한 변경을 포함할 수 있다.

ALTER TABLE 테이블명
	MODIFY(칼럼명1 데이터유형 [기본 값] [NOT NULL]
       [,  칼럼명2 데이터유형 [기본 값] [NOT NULL]
        , ...]);

ALTER TABLE DE
	MODIFY(DNAME VARCHAR2(15) NOT NULL
);

DESC DE;

칼럼을 변경할 때는 몇 가지 사항을 고려해 변경해야 한다.

해당 칼럼의 크기를 늘릴 수는 있지만, 테이블에 데이터가 존재한다면 칼럼의 크기를 줄이는 데는 제약이 있다. 기존의 데이터가 훼손될 수 있기 때문이다.
해당 칼럼이 NULL 값만 갖고 있거나 테이블에 아무 행도 없으면 칼럼의 크기를 줄일 수 있다.
해당 칼럼이 NULL 값만을 갖고 있으면 데이터 유형을 변경할 수 있다.
해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.
해당 칼럼에 NULL 값이 없ㅇ르 경우에만 NOT NULL 제약조건을 추가할 수 있다.

RENAME COLUMN
다음은 테이블을 생성하면서 만들었던 칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우에 유용하게 쓸 수 있는 RENAME COLUMN 구문이다.

ALTER TABLE 테이블명 RENAME COLUMN 기존 칼럼명 TO 새로운 칼럼명;

ALTER TABLE DE
RENAME COLUMN LOC TO HERE;

DESC DE;

RENAME COLUMN으로 칼럼명이 변경되면, 해당 칼럼과 관계된 제약조건에 대해서도 자동으로 변경되는 장점이 있다. 하지만 ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시돼 있는 기능이 아니고 Oracle 등 일부 DBMS에서만 지원한다.

라. DROP CONSTRAINT

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

테이블 생성 시 부여했던 제약조건을 삭제하는 명령어 형태는 다음과 같다.

마. ADD CONSTRAINT

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해 제약조건을 추가할 수 있다. 다음은 특정 칼럼에 제약조건을 추가하는 명령어 형태다.

RENAME TABLE

RENAME 명령어를 사용해 테이블의 이름을 변경할 수 있다.

RENAME DE TO DEP;

DESC DEP;

DROP TABLE

CREATE TABLE DEP2 AS SELECT * FROM DEP;
SELECT * FROM DEP2;

하기 전에 테이블을 하나 더 생성한다.

테이블을 잘못 만들었거나 테이블이 더이상 필요 없을 경우 해당 테이블을 삭제해야 한다. 다음은 불필요한 테이블을 삭제하는 명령이다.

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

DROP TABLE DEP2;
SELECT * FROM DEP2;
DESC DEP2;

DROP 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제한다. CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제함을 의미한다.

TRUNCATE TABLE

TRUNCATE TABLE은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어 있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다.

CREATE TABLE DEP2 AS SELECT * FROM DEP;
SELECT * FROM DEP2;

하기 전에 테이블을 하나 더 생성한다.

TRUNCATE TABLE 테이블명;

TRUNCATE TABLE DEP2;
SELECT * FROM DEP2;
DESC DEP2;