[ORACLE] [오라클 문법] 06. CREATE TABLE(테이블 생성) 사용방법 & 예제

DB작업을 하다 보면 빈번하진 않지만 목적에 맞게 별도의 테이블을 생성해야 하는 경우가 발생됩니다. 테이블이 발생되는 경우는 기존의 테이블 구조가 비효율적이어서 개선이 필요한 경우 작업을 하는 경우도 있지만 사용자의 요구에 따라 테이블을 생성해야 하는 필요성이 발생되는 경우가 많다고 생각합니다.

 

이와 같이 '어떤' 이슈로 인해 테이블을 만들어야 할 때, 테이블은 어떻게 만들어야 할지 알아보도록 하겠습니다.

이번 포스팅에서는 테이블의 구조와 테이블을 생성하는 기본적인 방법에 대해서 포스팅을 진행할 것이며, 테이블 생성 시 사용되는 기본키, 외래키, 테이블 코멘트 등은 별도 포스팅에서 다루도록 하겠습니다.

 

1. 테이블의 구조

테이블을 만들 때는 가장 기본이 되는 테이블에 담길 컬럼명과 자료형이 있을 수 있고, 중복을 허용하지 않아야 하는 테이블의 경우에는 기본키 값을 설정할 수 있습니다. 이번에는 주로 사용되는 테이블의 주요 섹션을 살펴보도록 하겠습니다.

(1) CREATE TABLE

CREATE TABLE 테이블명 (
컬렴명 데이터타입 NULL 여부
);

--예시--

CREATE TABLE COMPANY_INFO (
       COMPANY_SEQ	      NUMBER(3) NOT NULL,
       COMPANY_NAME           VARCHAR2(4000 BYTE) NOT NULL,
       COMPANY_ENGLISH_NAME   VARCHAR2(4000 BYTE) NULL,
       CLOSE_YN               CHAR(1 BYTE) NOT NULL,
       REG_DATE               DATE NOT NULL,
       CLOSE_DATE             DATE NULL
)

TABLE을 만들 때 반드시 있어야 하는 구분입니다. 해당 구문에서는 테이블의 이름과 해당 테이블에 포함되어야 할 컬럼명, 데이터 타입, NULL값을 허용할지 여부 등을 기록하게 됩니다.

(2) COMMENT

나 혼자 사용하고 나 혼자 이용할 테이블일 경우에는 테이블만 만들어줘도 큰 문제는 없을 수 있습니다. 그러나 대부분의 경우 다른 개발자분들과 협업을 통해 일을 진행하는 경우가 많습니다. 사실 나 혼자 이용할 목적으로 테이블을 만들었다고 할지라도 시간이 지나면 이 테이블이 무슨 목적으로 만들어졌는지 기억이 나지 않는 경우도 종종 있습니다.

그럴 때를 대비하여 테이블을 만들 때 COMMENT를 작성하여 테이블의 이름, 컬럼의 이름 등을 기록해 놓을 수 있습니다.

 

COMMENT ON TABLE 테이블명 IS '코멘트';
COMMENT ON COLUMN 테이블명.컬럼명 IS '코멘트';

--예시--
COMMENT ON TABLE COMPANY_INFO IS '회사정보';

COMMENT ON COLUMN COMPANY_INFO.COMPANY_SEQ IS '회사순번';
COMMENT ON COLUMN COMPANY_INFO.COMPANY_NAME IS '회사이름';
COMMENT ON COLUMN COMPANY_INFO.COMPANY_ENGLISH_NAME IS '회사영문명';
COMMENT ON COLUMN COMPANY_INFO.CLOSE_YN IS '폐업여부(폐업 : Y, 영업중 : N)';
COMMENT ON COLUMN COMPANY_INFO.REG_DATE IS '등록일자';
COMMENT ON COLUMN COMPANY_INFO.CLOSE_DATE IS '폐업일자';

 

COMMNET의 경우 테이블명에 대한 코멘트와 테이블의 컬럼에 대한 코멘트로 분리하여 작성할 수 있습니다. 사용되는 명령문의 경우 테이블은 COMMENT ON TABLE로 시작되며, 컬럼은 COMMENT ON COLUMN으로 시작되는 것에 차이점이 있습니다. COMMENT의 경우 테이블을 생성하는 과정에서 작성하지 않는 경우도 있지만, 가급적이면 COMMENT를 작성해주시는 것을 권해드립니다. 그 이유는 CLOSE_YN의 COMMENT를 보면 알 수 있는데요. 해당 COMMENT를 통해 우리는 CLOSE_YN의 값이 'Y'이면 폐업상태 값을, 'N'이면 영업 중을 의미한다는 것을 파악할 수 있듯이 COMMENT를 작성하면 각 데이터가 의미하는 값이 어떤지를 소스를 분석하지 않고도 쉽게 찾을 수 있는 장점이 있습니다.

(3) INDEX

INDEX는 색인을 의미하는데요. 그 뜻 그대로 INDEX는 많은 양의 데이터를 보다 쉽게 검색할 수 있도록 도와주는 역할을 담당하고 있습니다. 데이터양이 적으면 INDEX를 넣지 않는 경우도 있겠지만 INDEX를 넣어주면 데이터를 조회할 경우 조회 속도에 차이점을 느낄 수 있습니다. 특히 데이터양이 많을 경우에는 INDEX값을 넣지 않게 되면 조회 속도가 현저히 떨어지게 되니 사실상 거의 필수적인 구문이라고 생각하셔도 됩니다. 주요 표현식은 아래와 같습니다.

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명 (인덱스 컬럼명);

--예시--
CREATE UNIQUE INDEX IDX_UNIQUE ON COMPANY_INFO (COMPANY_SEQ);

CREATE INDEX IDX_GENERAL_1 ON COMPANY_INFO(COMPANY_NAME, COMPANY_ENGLISH_NAME);

해당 INDEX 중 UNIQUE의 경우 인덱스의 대상이 되는 컬럼 데이터가 중복된 데이터가 있으면 안 될 때 만들 수 있습니다.  해당 INDEX는 보통 기본키 값에 해당하는 컬럼에 설정하는 경우가 많으며, UNIQUE = 기본키 컬럼을 의미하진 않습니다. 

UNIQUE가 INDEX가 아닐 경우 INDEX값이 중복되어도 생성이 가능합니다. 

(4) 기본키(Primary Key), 외래키(Foreing Key)

기본키의 경우 해당 테이블에 있는 데이터를 식별하는 유일한 KEY에 해당하는 컬럼을 의미합니다. 보통 순번, 신청번호 등으로 표현되며 테이블의 구조에 따라 여러 컬럼을 묶어서 기본키를 사용하기도 합니다.

외래키의 경우 보통 자식 테이블에 많이 사용하는데요. 자식 테이블의 데이터는 얼마든지 지울 수 있지만, 자식 테이블에 데이터가 있음에도 부모 테이블의 데이터를 지우고자 시도할 때 이를 방지하기 위한 목적으로 사용됩니다. 

--기본키--
ALTER TABLE 테이블명
ADD CONSTRAINT 기본키이름 PRIMARY KEY(컬럼명);
--기본키 예제--
ALTER TABLE COMPANY_INFO
ADD CONSTRAINT PK_COMPANY_INFO PRIMARY KEY(COMPANY_SEQ);

--외래키--
ALTER TABLE 테이블명
ADD CONSTRAINT 외래키이름 FOREIGN KEY(컬럼명)
REFERENCES 부모테이블 (부모테이블 컬럼명);

--외래키 예제--
ALTER TABLE COMPANY_INFO
ADD CONSTRATINT FK_COMPANY_INFO FOREIGN KEY(COMPANY_SEQ)
REFERENCES TOP_COMPANY (TOP_COMPANY_SEQ);

 

기본키와 외래키의 생성 구문은 꼭 위와 같이 1개의 방식으로만 기본키와 외래키를 생성하진 않습니다.

이번 포스팅에서는 대략적인 테이블 구조를 보여드리기 위해 작성한 내용이며, 보다 상세한 내용은 별도의 포스팅을 통해 다루도록 하겠습니다.

 

여기까지의 내용을 확인하셨다면 기본적인 테이블 생성 시 알고 있어야 할 내용들은 전부 숙지한 상태입니다. 

이밖에도 테이블별로 다른 사용자에게 접근을 허락할 수 있는 권한을 부여할 수 있는 내용도 있지만 해당 내용까지 작성하면 내용이 너무 많아지기에 이번 포스팅은 여기까지만 작성하도록 하겠습니다.

2. 테이블 생성하기

이제 위의 예제를 바탕으로 COMPANY_INFO 테이블을 직접 만들어 보도록 하겠습니다.

(1) 기본 테이블 생성하기

사실 테이블의 경우 CREATE TABLE만 작성하면 가장 기본적인 테이블은 생성할 수 있습니다. 아래의 SQL문을 실행해주세요.

CREATE TABLE COMPANY_INFO (
       COMPANY_SEQ	      NUMBER(3) NOT NULL,
       COMPANY_NAME           VARCHAR2(4000 BYTE) NOT NULL,
       COMPANY_ENGLISH_NAME   VARCHAR2(4000 BYTE) NULL,
       CLOSE_YN               CHAR(1 BYTE) NOT NULL,
       REG_DATE               DATE NOT NULL,
       CLOSE_DATE             DATE NULL
);

해당 SQL문을 실행하면 COMPANY_INFO 테이블이 생성되었다는 메시지를 확인할 수 있는데요.

정말 COMPANY_INFO 테이블이 생성되었는지 확인하기 위해 아래의 SQL문을 실행해 보도록 하겠습니다.

SELECT * 
  FROM COMPANY_INFO;

 

해당 SQL문을 실행해보면 위와 같이 COMPANY_INFO 테이블의 컬럼명이 조회됨을 확인할 수 있습니다.

이제 기존에 배웠던 SELECT, INSERT, UPDATE, DELETE문을 통해 데이터를 직접 관리할 수 있는 환경이 조성되었습니다.

(2) COMMENT, INDEX, PK 추가하기

COMPANY_INFO의 테이블이 만들어졌기 때문에 해당 테이블만을 가지고도 데이터 처리를 함에 있어 큰 문제는 없습니다. 다만 데이터의 컬럼이 의미하는바, 또는 INDEX, PK 등의 테이블을 보다 안정적으로 운용할 수 있는 다른 장치는 마련되어 있지 않아 해당 테이블에 데이터가 수천 건, 수만 건이 쌓이게 되면 속도 저하는 물론 중복된 값이 관리되지 않는 등의 여러 문제점이 발생될 수 있습니다. 따라서 지금부터는 새롭게 만들어진 COMPANY_INFO 테이블에 COMMENT, INDEX, PK값을 직접 추가해보도록 하겠습니다.

 

아래의 SQL문을 실행시켜주세요.

COMMENT ON TABLE COMPANY_INFO IS '회사정보';

COMMENT ON COLUMN COMPANY_INFO.COMPANY_SEQ IS '회사순번';
COMMENT ON COLUMN COMPANY_INFO.COMPANY_NAME IS '회사이름';
COMMENT ON COLUMN COMPANY_INFO.COMPANY_ENGLISH_NAME IS '회사영문명';
COMMENT ON COLUMN COMPANY_INFO.CLOSE_YN IS '폐업여부(폐업 : Y, 영업중 : N)';
COMMENT ON COLUMN COMPANY_INFO.REG_DATE IS '등록일자';
COMMENT ON COLUMN COMPANY_INFO.CLOSE_DATE IS '폐업일자';

CREATE UNIQUE INDEX IDX_UNIQUE ON COMPANY_INFO (COMPANY_SEQ);

ALTER TABLE COMPANY_INFO
ADD CONSTRAINT PK_COMPANY_INFO PRIMARY KEY(COMPANY_SEQ);

 

해당 SQL문은 순서대로 COMPANY_INFO의 COMMENT정보를 등록해주고, 이후 INDEX문과 기본키를 생성해주었습니다. 해당 SQL문을 정상적으로 실행했다면 COMPANY_INFO 테이블의 정보가 변경되었다는 메시지를 확인할 수 있는데요. 이제 SQL DEVELOPER 프로그램을 통해 우리가 작성한 내용이 정상적으로 반영되었는지를 살펴보겠습니다.

(3) COMMENT 확인

SQL DEVELOPER 왼쪽의 테이블을 살펴보면 우리가 만든 COMPANY_INFO 테이블이 있습니다.

해당 테이블을 클릭해주세요.

COMPANY_INFO 테이블을 클릭하면 위와 같이 테이블 컬럼에 대한 기본정보를 확인할 수 있습니다.

먼저 우리가 작성한 테이블 이름에 대한 COMMNET가 정상적으로 반영되었는지 확인해보겠습니다.

위의 이미지에서 표시한 '작업' 부분을 클릭해주세요.

작업 부분을 클릭하면 위와 같은 메뉴가 나타나게 되는데요. 테이블 -> 설명 부분을 클릭해주세요.

테이블-> 설명 부분을 클릭하면 위와 같이 해당 테이블의 COMMENT가 나타납니다.

테이블을 만든 본인은 해당 테이블이 어떤 역할을 하는지 알 수 있지만, 테이블을 처음 접한 다른 사람들의 경우 위와 같이 테이블에 COMMENT가 있을 경우 해당 테이블의 성격을 보다 쉽게 파악할 수 있습니다. 

다음과 같이 말이죠.

"아~ 이 테이블은 SCOTT라는 사용자가 소유하고 있고, 테이블명은 COMPANY_INFO이고, 이 테이블은 회사 정보 데이터가 관리되는 테이블이구나."

테이블의 설명을 확인했다면, 이제 각 컬럼의 COMMENT를 확인해야겠죠?

눈치 빠르신 분들은 이미 아셨겠지만, 맨 처음 COMPANY_INFO 테이블을 클릭했을 때 나타난 테이블 정보 우측에 각 컬럼별 COMMENT가 작성되어 있음을 확인할 수 있으셨을 겁니다. 

(4) INDEX

이제 INDEX를 확인해보겠습니다.

COMPANY_INFO 상단 메뉴 중 '인덱스'로 표시된 메뉴가 있습니다. 해당 메뉴를 클릭하면 위와 같이 COMPANY_INFO 테이블에 설정된 INDEX정보가 나타납니다.

해당 내용을 보면 INDEX_NAME, UNIQUENESS, COLUMN_NAME등 우리가 작성한 주요 INDEX 내용이 잘 적용되어 있음을 확인할 수 있습니다.

(5) PK

이제 마지막으로 우리가 설정했던 PK(PRIMARY KEY를 확인해보도록 하겠습니다.

PK를 확인하기 위해서 COMPANY_INFO의 상단 메뉴 중 '제약 조건'을 클릭합니다. 

제약조건을 클릭하면 우리가 작성했던 PK정보를 확인할 수 있는데요.

CONSTRAINT_NAME을 통해 우리가 작성한 PK의 이름을 확인할 수 있고,

하단의 COLUMN_NAME을 통해서 해당 PK가 어떤 컬럼을 PK로 설정했는지를 확인할 수 있습니다.

 

 

3. 마치며

우리는 이번 포스팅을 통해서 테이블을 생성할 때 어떤 요소들이 사용되는지, 그리고 간단한 실습을 통해 테이블을 직접 만들어보기까지 했습니다.

테이블 생성은 우리가 집에서 직접 소규모 개발을 하거나, 스타트업 회사가 아닌 이상 보통은 테이블을 생성하는 작업은 하지 않습니다. 그 이유는 크게 2가지 정도로 생각해볼 수 있는데요.

 

첫 번째는 보통 회사에서는 저마다 이미 사용하고 있는 테이블을 가지고 있습니다. 따라서 새로운 비즈니스 로직이 추가되거나, 새로운 사업을 하지 않는 이상 기존 테이블을 유지관리하는데 포커스가 맞춰져 있지 빈번하게 테이블을 생성하고 삭제하는 경우는 드문 편이라고 생각합니다. 

 

두 번째는 테이블 생성은 선임 직원분들이 하는 경우가 많습니다. 우리가 테이블을 설계할 때는 보통 일상생활에서 일어나는 다양한 비즈니스를 프로세스화하고 이를 데이터로 구분한 뒤 테이블을 설계하게 됩니다. 그렇기 때문에 테이블을 생성할 때는 여러 가지 요소를 살펴봐야 하고, 그중에서 데이터화 해야 할 데이터와 그렇지 않은 데이터를 구분하는 작업도 필요합니다. 이와 같이 테이블 생성은 여러 경우의 수와 데이터 간의 관계가 정립된 뒤에야 만들게 되는데요. 이러한 작업은 관련 지식은 물론 경험적인 내공도 필요하기 때문에 보통 테이블 생성은 선임 직원분들이 하는 경우가 많아 그 역할을 신입직원에게 맡기는 경우는 드문 편이라고 생각합니다. 

 

그럼에도 불구하고 테이블 생성에 관한 내용을 확인하는 이유는 해당 내용을 모를 경우 기존에 설정되어 있는 테이블 구조를 파악할 수 없고, 간혹 가다 작은 비즈니스 로직이 변경 및 추가되면 테이블을 만들 수 있는 기회가 주어지기 때문에 해당 내용은 기본적으로 숙지하고 있는 것이 그렇지 않은 것보다 업무를 함에 있어 많은 이익이 된다고 생각합니다.

 

다음에는 이렇게 만들어진 테이블 구조의 각 부분 부분을 하나하나 뜯어보며 보다 상세한 내용을 작성해보도록 하겠습니다.