ORACLE 테이블 생성/수정/삭제/복사

오라클 테이블 생성, 수정, 삭제, 복사에 대해서 알아보겠습니다. 테이블은 데이터가 저장이 되는 가장 기본이 되는 단위입니다. 테이블은 쉽게 설명해서 데이터를 담는 그릇이라고 생각하시면 됩니다. 데이터를 어떤 모양의 칼럼으로 담을지를 결정하고 테이블을 생성하게 됩니다.

오라클 테이블 생성,수정,삭제,복사

오라클 테이블 생성

오라클 테이블 생성은 CREATE TABLE 구분으로 쉽게 생성할 수 있습니다. 테이블을 생성할 때 유의 해야 하는 점은 접속한 유저 기준으로 테이블이 생성된다는 점입니다. 계정을 여러 개 사용하고 있을 경우 어느 계정에 테이블을 생성할지 생각하고 해당 계정에 테이블을 만들어 줘야 합니다. 다음은 테이블 생성 예제입니다.

CREATE TABLE 테이블명 (
    컬럼명1  컬럼데이터타입 [DEFAULT 기본값] [NULL | NOT NULL] [UNIQUE] [PRIMARY KEY],
    컬럼명2  컬럼데이터타입 [DEFAULT 기본값] [NULL | NOT NULL] [UNIQUE] [PRIMARY KEY],
    ...
    [테이블 제약조건]
);

테이블을 생성할 때 칼럼명과 칼럼데이터타입 지정 후 여러 옵션을 지정할 수 있습니다. 해당 칼럼이 값이 없이 저장될 때 자동으로 값을 넣어주는 DEFAULT 옵션이 있고 이 칼럼을 NULL을 허용할지 말지를 결정하는 옵션이 지정됩니다. NULL옵션은 지정하지 않으면 디폴트 옵션은 NULL허용입니다. UNIQUE는 해당칼럼에 저장되는 값은 모두 고유해야 되는 값입니다. PRIMARY KEY는 테이블의 기본키입니다. 기본키를 지정하면 그 칼럼은 자동으로 NOT NULL, UNIQUE 제약조건이 지정됩니다. 또한 칼럼단위로 PRIMARY KEY를 지정하는 경우 단일 칼럼에만 지정할 수 있습니다. 여러 개의 칼럼을 묶어서 복합키로 지정하는 경우 [테이블 제약조건] 이 부분에 복합키를 선언해 주어야 합니다.

-- 테이블을 생성 합니다.
CREATE TABLE TBL_TEST (
    COL1  NUMBER(10)  PRIMARY KEY,
    COL2  VARCHAR(2)  NOT NULL UNIQUE,
    COL3  VARCHAR(10) DEFAULT 'A'
    COL4  DATE
) TABLESPACE HR_TBLSPACE;

COL1은 NUMBER타입 기본키로 지정이 되었습니다. 데이터가 저장이 될 때 NULL로 데이터가 들어온다거나 중복되는 데이터가 생기면 오류가 발생하게 됩니다. COL2도 NOT NULL에 UNIQUE라 COL1처럼 NULL, 중복데이터는 허용하지 않습니다. COL3은 데이터가 없이 저장이 될 경우 'A'로 자동 저장이 됩니다. DEFAULT값은 데이터 저장 시에만 적용받습니다. NULL로 수정하는 경우 값은 그냥 NULL입니다. COL4는 조건이 아무것도 없으므로 NULL허용으로 데이터가 없어도 저장이 잘 됩니다. 테이블은 테이블스페이스에 저장이 됩니다. 테이블스페이스는 데이터를 저장할 때 사용하는 논리적인 영역입니다. 마지막 구문은 테이블 마지막에 TABLESPACE 테이블스페이명을 지정해 주면 해당 테이블스페이스로 테이블을 저장하겠단 소리입니다. TBL_TEST테이블은 HR_TBLSPACE에 저장되는군요. 이번엔 하단에 복합키나 다른 제약조건을 걸어보겠습니다.

-- 복합키 지정하는 방법 입니다.
CREATE TABLE TBL_TEST1 (
    COL1  NUMBER(10),
    COL2  VARCHAR(2) NOT NULL,
    COL3  VARCHAR(10),
    COL4  VARCHAR(10) DEFAULT 'A',
    CONSTRAINT PK_TBL_TEST1 PRIMARY KEY (COL1, COL2)
) TABLESPACE HR_TBLSPACE;

-- 복합키, 참조키, 체크제약조건을 같이 지정하는 방법 입니다.
-- 제약조건은 단일 또는 복합으로 지정할 수 있습니다.
CREATE TABLE TBL_TEST2 (
    COL1  NUMBER(10),
    COL2  VARCHAR(2) NOT NULL,
    COL3  VARCHAR(10),
    COL4  NUMBER(10),
    CONSTRAINT PK_TBL_TEST2 PRIMARY KEY (COL1, COL2)
    CONSTRAINT FK_TBL_TEST2_COL3 FOREIGN KEY (COL3) REFERENCES USERS(USER_NAME)
    CONSTRAINT CHK_TBL_TEST2 CHECK (COL4 != 10)
) TABLESPACE HR_TBLSPACE;

참조키(FOREIGN KEY)는 다른 테이블의 기본키나 유니크키가 지정된 칼럼과 연결될 수 있습니다. 또한 참조하는 테이블의 칼럼과 참조되는 테이블의 칼럼이 데이터형식과 크기가 동일해야 합니다. 연결되는 두 테이블의 데이터 관계도 1:N 또는 N:1인경우나 참조키를 사용할 수 있습니다. CHECK 제약조건은 해당조건을 만족해야 데이터를 저장할 수 있습니다. 위의 예제에서 COL4 != 10으로 CHECK 제약조건이 걸려 있으므로 COL4는 10의 데이터가 들어오면 제약조건 위반 오류가 발생합니다.

오라클 테이블 수정

오라클 테이블 수정은 ALTER TABLE로 수정할 수 있습니다. 테이블명 변경, 칼럼 추가, 변경, 삭제 등을 실행할 수 있습니다. 제약조건도 추가할 수 있습니다.

-- 테이블명을 변경하는 예제입니다. (TBL_TEST에서 TBL_TEST2로 변경)
ALTER TABLE TBL_TEST RENAME TO TBL_TEST2;

-- 테이블 컬럼 추가하는 예제 입니다.
ALTER TABLE TBL_TEST
ADD COLUMN COL5 VARCHAR2(10);

-- 테이블 컬럼 수정하는 예제 입니다.
ALTER TABLE TBL_TEST
MODIFY COLUMN COL5 VARCHAR2(5);

-- 테이블 컬럼 삭제하는 예제 입니다.
ALTER TABLE TBL_TEST
DROP COLUMN COL5;

-- 테이블 컬럼 제약조건 추가하는 예제입니다.
ALTER TABLE TBL_TEST
ADD CONSTRAINT CHK_COL5 CHECK (COL5 != 0);

테이블 칼럼 수정 시 주의해야 할 점이 있습니다. 타입이 바뀌는 경우 칼럼 수정 시 오류가 발생할 수 있고 같은 타입이라도 사이즈를 늘리는 건 상관없지만 사이즈를 줄이는 경우는 데이터가 유실될 가능성이 있습니다. 예를 들어 VARCHAR2 10자리에서 5자리로 줄이는 경우 앞에 5글자만 남고 뒤에 5글자는 손실됩니다. 데이터베이스에서 데이터 유실은 엄청나게 큰 문제이므로 꼭 주의하셔야 합니다. 또 한 가지 주의하셔야 할 점은 이미 데이터가 저장이 되어 있는 상태에서 NULL허용으로 되어 있는 칼럼을 NOT NULL로 변경할 경우 NULL이 데이터로 들어가 있기 때문에 데이터를 전체 삭제하고 NOT NULL로 칼럼변경을 할 수 있습니다. DROP COLUMN의 경우도 칼럼이 아예 삭제가 돼버리기 때문에 백업은 필수입니다.

오라클 테이블 삭제

테이블 삭제는 곧 데이터를 지운다는 의미로 항상 삭제 전에 여러 방면으로 재검토를 하고 삭제하여야 합니다. 아래는 테이블 삭제하는 예제입니다.

-- 테이블 삭제 예제 입니다.
DROP TABLE TBL_TEST;

-- 테이블과 연관된 객체 모두를 삭제하는 예제입니다.
DROP TABLE TBL_TEST CASCADE;

CASCADE옵션의 경우 해당 테이블과 관련된 트리거, 인덱스, 제약조건 등 관련된 모든 객체를 같이 자동으로 삭제합니다.의도치 않게 삭제하려는 테이블에 관련된 객체가 자동으로 삭제가 될 수 있으니 꼭 CASCADE옵션 사용 시에는 면밀히 검토 후 사용하시길 권해드립니다.

오라클 테이블 복사

현업에서 테이블을 사용하다 보면 복사를 해야 하는 경우가 많이 발생합니다. 테이블 백업을 해둔다거나 비슷한 구조로 테이블을 생성해야 된다거나 할 때요. 이럴 때 테이블 복사를 사용하면 간단하게 테이블을 생성할 수 있습니다.

-- 신규테이블 NEW_CUSTOMER 및 데이터 복사
CREATE TABLE NEW_CUSTOMER AS
SELECT * FROM CUSTOMER;

-- 테이블 구조만 복사
CREATE TABLE NEW_CUSTOMER AS
SELECT * FROM CUSTOMER
WHERE 1 = 2;

오라클 테이블에 관해 살펴봤습니다. 유익한 정보 되셨길 바라요!