Oracle에서 'Create Table ... AS Select ...'을 사용해 테이블을 복사할 때, 제약 조건과 인덱스는 복사되지 않는다. 백업 테이블만들 때 주의해야한다.

문제

Oracle에서 백업 테이블을 만들 일이 있어, 아래와 같이 'Create Table ... AS Select ...'을 이용해 백업 테이블을 만들었다.

CREATE TABLE test_backup_20230823 AS SELECT * FROM test;

 

아래는 test 테이블의 스키마이다.

create table TEST
(
    ID       VARCHAR2(10 char)                                not null,
    COUNTRY    VARCHAR2(10 char),
    NAME     VARCHAR2(100 char),
    USE_YN        CHAR                        default 'Y',
    REG_USER      VARCHAR2(10 char)           default 'SYSTEM'     not null,
    REG_TIME      TIMESTAMP(6) WITH TIME ZONE default SYSTIMESTAMP not null
)
/

create unique index XPK_TEST
    on TEST (ID)
/

alter table TEST
    add primary key (ID)
/

 

백업용으로 만든 test_backup_20230823 테이블에 새로운 데이터를 insert하니, default로 설정된 값들이 null로 들어갔다.

 

원인

'Create Table ... AS Select ...' 문은 테이블의 데이터 및 구조를 복사하지만, 기본값, 기본 키, 외래 키, 제약 조건, 인덱스 등과 같은 추가적인 요소는 복사하지 않는다. 이 명령어는 단순히 원본 테이블의 데이터를 선택하여 새로운 테이블로 복사한다.

 

따라서, 백업 테이블을 만든 후에 기본값, 기본 키, 외래 키, 제약 조건, 인덱스는 따로 추가해줘야 한다.

 

복사 직후 test_backup_20230823 테이블의 스키마를 보면 기본값, 기본키, 인덱스 설정이 없는 것을 확인할 수 있다.

create table TEST_BACKUP_20230823
(
    ID       VARCHAR2(10 char)           not null,
    COUNTRY  VARCHAR2(10 char),
    NAME     VARCHAR2(100 char),
    USE_YN   CHAR,
    REG_USER VARCHAR2(10 char)           not null,
    REG_TIME TIMESTAMP(6) WITH TIME ZONE not null
)
/

 

해결

백업 테이블에 원본 테이블에 있는 기본값, 기본키, 인덱스를 따로 설정해주었다.

 

기본값 설정

alter table TEST_BACKUP_20230823 modify (USE_YN default 'Y');
alter table TEST_BACKUP_20230823 modify (REG_USER default 'SYSTEM');
alter table TEST_BACKUP_20230823 modify (REG_TIME default SYSTIMESTAMP);

 

인덱스 설정

create unique index XPK_TEST_BACKUP_20230823
    on TEST_BACKUP_20230823 (ID)

 

기본값 설정

alter table TEST_BACKUP_20230823
    add primary key (ID)

 

변경 후 test_backup_20230823 테이블의 스키마

create table TEST_BACKUP_20230823
(
    ID       VARCHAR2(10 char)                                not null,
    COUNTRY  VARCHAR2(10 char),
    NAME     VARCHAR2(100 char),
    USE_YN   CHAR                        default 'Y',
    REG_USER VARCHAR2(10 char)           default 'SYSTEM'     not null,
    REG_TIME TIMESTAMP(6) WITH TIME ZONE default SYSTIMESTAMP not null
)
/

create unique index XPK_TEST_BACKUP_20230823
    on TEST_BACKUP_20230823 (ID)
/

alter table TEST_BACKUP_20230823
    add primary key (ID)
/