[Oracle] constraint 정리

테이블 생성

CREATE TABLE 테이블명 (
    컬럼명    데이터 타입    제약조건,
    컬럼명    데이터 타입    제약조건
    .
    .
    .
);

 

테이블 삭제

테이블은 물론 테이블 내의 모든 데이터 삭제
"CASCADE CONSTRAINTS"를 붙이면 테이블에 정의되어있는 모든 제약조건도 자동으로 삭제

-- 테이블과 테이터 삭제
DROP TABLE 테이블명;

-- 제약조건까지 삭제
DROP TABLE 테이블명 CASCADE CONSTRAINTS;

-- 삭제한 테이블을 휴지통에 넣지 않고 삭제
DROP TABLE 테이블명 PURGE;

-- 테이블, 데이터, 제약조건 바로 삭제
DROP TABLE 테이블명 CASCADE CONSTRAINTS PURGE;

 

테이블 변경

테이블 이름 변경

ALTER TABLE 변경전_테이블명 RENAME TO 변경후_테이블명;

 

컬럼 이름 변경

ALTER TABLE 테이블명 RENAME COLUMN 변경전_컬럼명 TO 변경후_컬럼명;

 

컬럼 데이터 타입 변경

ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입;

 

컬럼 추가

컬럼은 맨뒤에 추가된다. 컬럼 사이에 추가하고 싶으면 컬럼을 추가한 후 테이블 복사를 이용한다.

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;

 

컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

제약조건 추가

-- 컬럼 1개를 대상으로 하더라도 괄호로 감싸야한다
ALTER TABLE 테이블명 ADD CONSTRAINTS 제약조건명    제약조건    (컬럼1, 컬럼2, ...);

 

 

제약조건 삭제

-- 제약조건 이름을 검색
-- 오라클의 경우 USER_CONSTRAINTS에 제약조건 정보를 저장하며
-- 조회 조건의 테이블명은 반드시 대문자를 사용해야한다
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = '테이블명';

-- 제약조건 삭제
ALTER TABLE 테이블명 DROP CONSTRAINTS 제약조건명;

 

테이블 복사

테이블 구조 변경전 테스트하기 위해 복사하거나 백업이 필요한 경우

-- 테이블 구조와 데이터 모두 복사
CREATE TABLE 생성할_테이블명 AS
SELECT 컬럼1, 컬럼2, ...
FROM 복사할_테이블명;

 

테이블 구조를 변경할때는 테이블을 복사하고 기존 테이블을 삭제한 뒤 복사한 테이블의 이름을 기존 테이블 이름으로 변경한다.

-- 테이블 구조와 데이터 모두 복사
CREATE TABLE new_table AS
SELECT 컬럼3, 컬럼1, 컬럼4, ...
FROM test_table;

-- 기존 테이블 삭제
DROP TABLE test_table CASCADE CONSTRAINTS;

-- 복사한 테이블의 이름을 변경
ALTER TABLE new_table RENAME TO test_table;

 

테이블 생성 예

DROP TABLE table_name CASCADE CONSTRAINTS PURGE;
CREATE TABLE table_name (
    column1 VARCHAR2(64)
  , column2 NUMBER(10,0) DEFAULT 0
  , column3 DATE DEFAULT SYSDATE
  , column4 TIMESTAMP(6) DEFAULT SYSTIMESTAMP
) TABLESPACE tablespace_name;
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column1, column2)
USING INDEX TABLESPACE tablespace_name;
CREATE INDEX index_name ON table_name(column3, column4) TABLESPACE tablespace_name;
COMMENT ON COLUMN table_name.column1 IS 'comment of column1';
COMMENT ON COLUMN table_name.column2 IS 'comment of column2';
COMMENT ON COLUMN table_name.column3 IS 'comment of column3';
COMMENT ON COLUMN table_name.column4 IS 'comment of column4';
COMMENT ON TABLE table_name IS 'comment of table_name';