[Oracle SQL] DDL - 테이블 변경(ALTER) 및 삭제(DROP)

데이터베이스에서 테이블을 변경하고 삭제하는 방법을 알아보겠습니다.

 

 

테이블 삭제 (DROP)

 

앞서 dept02 테이블과 그 테이블을 참조하는 emp02 테이블을 생성했습니다. 다음과 같이 부모 테이블인 dept02 테이블을 삭제하면 오류가 발생합니다.

DROP TABLE dept02;  --> 오류
--> ORA-02449: unique/primary keys in table referenced by foreign keys

 

이때 CASCADE CONSTRAINTS 옵션을 사용해서 삭제하면, 자식 테이블의 외래키 제약 조건도 연쇄적으로 삭제되어 부모 테이블을 삭제할 수 있게 됩니다.

DROP TABLE dept02 CASCADE CONSTRAINTS;

 

 

 

테이블 변경 (ALTER)

 

생성된 테이블의 구조를 변경할 수 있습니다. 컬럼의 추가, 삭제 및 컬럼의 타입이나 길이 변경, 제약 조건 추가 및 삭제 등이 가능합니다. emp 테이블의 구조와 데이터를 복사하여 emp04 테이블을 생성해 보겠습니다.

CREATE TABLE emp04
AS
SELECT * FROM emp;

 

 

1. 컬럼

 

1-1. 컬럼 추가 (ALTER TABLE ADD)

 

ALTER TABLE ADD 문으로 기존 테이블에 컬럼을 추가할 수 있습니다. 

ALTER TABLE 테이블명
ADD (컬럼명 데이터타입 [, 컬럼명 데이터타입]);

 

emp04 테이블에 문자 타입의 이메일과 주소 컬럼을 추가해 보겠습니다.

ALTER TABLE emp04
ADD(email VARCHAR2(20), address VARCHAR2(20));

추가된 컬럼을 확인할 수 있습니다.

 

 

1-2. 컬럼 변경 (ALTER TABLE MODIFY)

 

ALTER TABLE MODIFY 문으로 기존 컬럼의 데이터 타입이나 크기, DEFAULT 값을 변경할 수 있습니다. 단 컬럼 길이를 축소하거나 데이터 타입을 변경할 때는 모든 행이 null값이거나 행이 없는 경우에만 가능합니다. DEFAULT 값을 변경할 경우에는 변경 이후부터 입력되는 행에 대해서 적용됩니다. 

ALTER TABLE 테이블명
MODIFY (컬럼명 데이터타입 [, 컬럼명 데이터타입]);

 

이메일 컬럼의 크기를 40byte로 변경할 수 있습니다. 

ALTER TABLE emp04
MODIFY (email VARCHAR2(40));

 

 

1-3. 컬럼 삭제 (ALTER TABLE DROP)

 

ALTER TABLE DROP 문으로 기존 컬럼을 삭제할 수 있습니다. 

ALTER TABLE 테이블명
DROP (컬럼명 [, 컬럼명]);

 

emp04 테이블에서 이메일과 주소 컬럼을 삭제하는 SQL문입니다.

ALTER TABLE emp04
DROP (email, address);

 

 

 

2. 제약 조건

 

2-1. 제약 조건 추가 (ALTER TABLE ADD)

 

ALTER TABLE ADD 문을 사용하여 제약 조건을 추가합니다. 

ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건타입(컬럼명);

 

dept 테이블을 이용해 dept03 테이블을 생성해 보겠습니다.

CREATE TABLE dept03
 (deptno NUMBER(2),
 dname VARCHAR2(15),
 loc VARCHAR2(15)
 );

 

다음과 같이 부서 번호 컬럼에 기본키를 추가할 수 있습니다.

ALTER TABLE dept03
ADD CONSTRAINT dept03_deptno_pk PRIMARY KEY(deptno);

 

그런데 이 문법은 테이블 레벨에서 제약 조건을 설정하는 것과 동일한 문법이기 때문에, 컬럼 레벨에서만 설정이 가능한 NOT NULL 제약 조건을 추가할 때는 이 문법을 사용할 수 없습니다. 대신 ALTER TABLE MODIFY 문을 사용하여 기존 컬럼을 수정하는 방식으로 NOT NULL 제약 조건을 추가해야 합니다. 부서명 컬럼에 NOT NULL 제약 조건을 추가하는 SQL문입니다.

ALTER TABLE dept03
MODIFY (dname VARCHAR2(15) CONSTRAINT dept03_dname_nn NOT NULL);

 

 

2-2. 제약 조건 삭제 (ALTER TABLE DROP)

 

ALTER TABLE DROP 문을 사용하여 기존 테이블의 제약 조건을 삭제합니다. 기본적으로 제약조건명을 지정하여 삭제하지만, PRIMARY KEY와 UNIQUE 제약 조건은 제약조건명 없이 키워드만으로 삭제 가능합니다. 이때 UNIQUE에는 컬럼명을 명시해 주어야 합니다. 

ALTER TABLE 테이블명
DROP PRIMARY KEY | UNIQUE(컬럼명) |
	CONSTRAINT 제약조건명 [CASCADE];

 

dept03 테이블의 기본키를 다음과 같이 두 가지 방법으로 삭제할 수 있습니다. 

ALTER TABLE dept03
DROP PRIMARY KEY;

ALTER TABLE dept03
DROP CONSTRAINT dept03_deptno_pk;

 

CASCADE 옵션을 사용하면 모든 종속적인 제약 조건을 같이 삭제할 수 있습니다. 실습을 위해 부서 번호를 기본키로 갖는 부모 테이블을 생성한 후, 10번 부서를 입력해 보겠습니다.

CREATE TABLE dept05
(deptno NUMBER(2) CONSTRAINT dept05_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15),
 loc VARCHAR2(15)
 );
INSERT INTO dept05 (deptno,dname,loc) VALUES (10,'인사','서울');
COMMIT;

 

dept05 테이블의 deptno를 참조하는 자식 테이블을 생성한 후, 10번 부서를 참조하는 사원 레코드를 입력해 보겠습니다.

CREATE TABLE emp05
(empno NUMBER(4) CONSTRAINT emp05_empno_pk PRIMARY KEY,
 ename VARCHAR2(15),
 deptno NUMBER(2) CONSTRAINT emp05_deptno_fk REFERENCES dept05(deptno)
 );
INSERT INTO emp05 (empno,ename,deptno) VALUES (1000,'John',10);
COMMIT;

 

데이터 사전에서 두 테이블이 갖는 제약 조건을 확인해 보겠습니다.

 

자식 테이블에서 참조하고 있는 부모 테이블의 기본키를 삭제할 경우 오류가 발생하는 것을 확인할 수 있습니다.

ALTER TABLE dept05
DROP PRIMARY KEY;  --> 오류
--> ORA-02273: this unique/primary key is referenced by some foreign keys

 

이러한 경우에 CASCADE 옵션을 사용하여 부모 테이블의 제약 조건 삭제 시 자식 테이블의 외래키 제약 조건도 연쇄적으로 삭제할 수 있습니다.

ALTER TABLE dept05
DROP PRIMARY KEY CASCADE;

 

데이터 사전에서 제약 조건이 연쇄적으로 삭제된 것을 확인할 수 있습니다.

 

 

2-3. 제약 조건 활성화/비활성화 (ALTER TABLE ENABLE | DISABLE)

 

ALTER TABLE ENABLE | DISABLE 문을 사용하여 제약 조건을 필요에 의해 활성화하거나 비활성화할 수 있습니다. 

ALTER TABLE 테이블명
ENABLE | DISABLE [CONSTRAINT 제약조건명] [CASCADE];

 

emp05 테이블의 기본키를 비활성화해 보겠습니다.

ALTER TABLE emp05
DISABLE CONSTRAINT emp05_empno_pk;

 

데이터 사전의 status 컬럼에 비활성화된 것이 표시되었습니다.

 

 

이상으로 DDL을 사용하여 테이블을 생성, 변경, 삭제하는 방법을 정리해 보았습니다.