오라클 테이블 이름 변경 및 삭제

 

10.4. 오라클 테이블 이름 변경 및 삭제

RENAME 명령으로 생성된 테이블 이름을 변경하고 DROP TABLE 명령으로 테이블을 삭제할 수 있습니다.

[기본 형식]

RENAME old_table_name TO new_table_name DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS]

테이블 이름 변경 및 삭제 원칙은 다음과 같습니다.

1. 삭제할 테이블은 사용자 계정에서 생성한 테이블이어야 합니다. 다른 스키마 계정의 테이블이면 DROP ANY TABLE 시스템 권한이 있어야 삭제할 수 있습니다.

2. 데이터 딕셔너리로부터 테이블 정의가 삭제되며 테이블 로우(ROW) 및 연관되어 생성된 인덱스, 트리거는 함께 삭제되지만 연관된 스키마 오브젝트 중 뷰 및 함수 및 프로시저는 삭제되지 않고 ‘INVALID’ 상태가 됩니다. 

3. 삭제되는 테이블의 기본 키primary key 또는 고유 키unique key를 자식 테이블에서 참조하고 있다면 삭제에 실패하게 됩니다. 그럴 때는 자식 테이블을 먼저 삭제하거나 DROP TABLE 명령어 마지막에 CASCADE CONSTRAINTS 조건을 주면 됩니다. CASCADE CONSTRAINTS 옵션은 삭제하는 테이블의 기본 키나 고유 키를 참조하는 참조 무결성 제약조건을 동시에 삭제하는 경우에 사용됩니다.

4. 삭제되는 테이블에 할당된 확장 영역은 테이블스페이스에 반환되며 다른 오브젝트 생성 시 사용할 수 있게 되고 삭제된 테이블은 플래시백 옵션으로 복구할 수 있습니다. 

이전에 실습한 CREATE TABLE ~ AS 구문으로 존재하는 테이블을 기본으로 새로운 테이블을 생성 후 이름을 변경하고, 테이블을 삭제하는 실습 그리고 부모, 자식 관계를 가지는 테이블에서 부모 또는 자식 테이블을 삭제하는 실습을 해보겠습니다.

실습

CREATE TABLE ~ AS 구문으로 존재하는 테이블을 기본으로 새로운 테이블을 생성하고 이름을 변경 후 삭제해 보겠습니다.

EMP 테이블에서 전체 테이블 구조만 복사하여 EMP10 테이블을 생성 후 이름을 EMP109로 변경하세요.(EMP10 테이블이 이미 존재한다면 삭제 후 실습하세요)

CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE 1 = 2;

RENAME EMP10 TO EMP109;

<실행결과>

테이블 이름이 변경되었습니다.

EMP109 테이블을 삭제하세요.

DROP TABLE EMP109;

<실행결과>

Table EMP109이(가) 삭제되었습니다.

실습

업무상 필요에 따라 사용자 계정의 전체 테이블을 삭제하는 스크립트를 생성하는 경우도 가끔 있습니다. 아래 실습에서 삭제 스크립트 생성하는 방법에 대해 살펴 보겠습니다.

현재 접속한 사용자의 테이블을 모두 삭제하는 DROP TABLE 스크립트를 생성하세요.

SELECT 'DROP TABLE  ' || TABLE_NAME || ';' AS "DROP SCRIPT"  

FROM USER_TABLES;

<실행결과>

	DROP SCRIPT
1	DROP TABLE  BONUS;
2	DROP TABLE CUSTOMER;
3	...

실습

이번에는 부모, 자식 관계를 가지는 테이블을 생성하여 DROP TABLE을 실습합니다.

deptno, dname 컬럼을 가지는 DEPT_TEST라는 테이블을 생성합니다. deptno 컬럼이 기본 키입니다. DEPT_TEST 테이블이 이미 존재한다면 삭제 후 실습하세요.

CREATE TABLE DEPT_TEST (

   DEPTNO NUMBER(2) CONSTRAINT PK_DEPT_TEST PRIMARY KEY,

   DNAME VARCHAR2(14)

) ;

empno, ename, deptno 컬럼을 가지는 EMP_TEST 테이블을 생성하세요. empno는 Primary Key 이고 deptno 컬럼은 외래 키foreign key로 DEPT_TEST 테이블의 deptno 컬럼을 참조합니다.

CREATE TABLE EMP_TEST (

   EMPNO NUMBER(4) CONSTRAINT PK_EMP_TEST PRIMARY KEY,

   ENAME VARCHAR2(100),

   DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO_TEST REFERENCES DEPT_TEST

);

EMP_TEST 테이블의 deptno 컬럼에 KEY를 내려주는 DEPT_TEST를 부모이고 받는 EMP_TEST가 자식입니다.

외래 키를 지정하는 이유는 EMP_TEST 테이블의 deptno 컬럼은 값이 없는 것은 받아주지만(NULL 허용 컬럼), 값이 들어온다면 DEPT_TEST 테이블의 DEPTNO값만 허용하려는 의도에서 입니다. 이것이 ‘참조하는 데이터는 무결해야 한다’는 데이터 무결성의 한 종류인 참조 무결성입니다. 

참고로 개체 무결성이라는 것은 개체, 테이블이 무결해야 한다는 의미입니다. 예를 들어 Primary Key 컬럼은 중복이 없어합니다, 사번이 같은 직원이 있으면 안되니 까요. 오라클에서 Primary Key를 지정하면 해당 컬럼에 대해 별도의 영역에 인덱스를 생성하고 PK  컬럼에 NOT NULL, UNIQUE 제약조건을 걸어줍니다. 이렇게 하면 새 행을 추가할 때 PK 컬럼에 값이 꼭 들어와야 하며 중복되어서도 안 됩니다(개체 무결성).

부모 테이블인 DEPT_TEST 삭제를 시도해봅시다.

DROP TABLE DEPT_TEST;

<실행결과>

ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다.

외래 키로 지정된 자식 테이블이 있기 때문에 부모를 먼저 지울 수는 없어 에러가 발생했습니다. 자식 DEMP_TEST 테이블을 먼저 삭제 후 DEPT_TEST 테이블을 삭제하는 것이 정상적인 방법입니다. 아래에서 제약조건을 비활성해서 삭제해봅니다. 

CASCASE CONSTRAINTS 옵션을 이용해 부득이 자식이 있는 DEPT_TEST를 삭제합시다.

DROP TABLE DEPT_TEST CASCADE CONSTRAINTS;

<실행결과>

Table DEPT_TEST이(가) 삭제되었습니다.

외래 키 제약조건을 삭제 후 테이블을 삭제했습니다. DEPT_TEST 테이블을 삭제 후 EMP_TEST 테이블의 테이블 생성 스크립트를 확인하면 다음과 같습니다(deptno 컬럼에 기술한 외래 키 제약조건이 사라졌습니다).

​

 

#테이블이름변경, #테이블삭제, #오라클테이블, #오라클, #ORACLE

오라클 테이블 구조 변경, ALTER TABLE, 컬럼추가, 삭제, 컬럼 이름 변경

 

오라클 테이블 구조 변경, ALTER TABLE, 컬럼추가, 삭제, 컬럼 이름 변경

10.3. 테이블 구조 변경

테이블 구조를 변경하는 데 DDL명령인 ALTER TABLE을 사용합니다. 주로 컬럼 타입 변경, 길이 변경, 컬럼 추가, 컬럼 삭제 등을 합니다.

10.3.1 컬럼 추가

테이블이 생성된 후 컬럼을 추가할 수 있습니다. 추가하는 컬럼은 오라클12C 이전에는 컬럼의 중간에 끼어들면서 추가 하는것은 불가능해서 테이블의 뒷부분에 추가되어 컬럼의 순서를 변경하려면 테이블을 삭제하고 다시 만들었지만 12C 이후 버전에서는 원하는 위치에 컬럼을 추가하는 방법을 제공 합니다.

컬럼이 추가될 테이블에 이미 데이터가 들어 있다면 추가되는 컬럼을 NOT NULL로 정의할 수 없습니다. 이미 존재하는 기존 행의 추가 컬럼은 NULL로 입력할 수 있습니다. 추가 컬럼은 테이블의 맨뒤에 추가되며 DEFAULT값을 지정할 수 있습니다.

[기본 형식]

ALTER  TABLE table_name ADD (column_name data_type [DEFAULT expr]…

사원emp테이블을 복제하여 새로운 테이블을 생성한 후 뒷부분에 컬럼을 추가하고 삭제하는 실습을 한 후 컬럼을 추가한 후 원하는 위치로 옮기는 실습을 오라클12C 이후와 이전으로 나누어서 진행해 보겠습니다.

실습

CREATE TABLE ~ AS 명령으로 존재하는 테이블의 컬럼구조나 데이터를 기반으로 새로운 테이블을 생성할 수 있습니다. 

EMP 테이블의 empno, ename 컬럼 및 데이터를 복사하여 EMP10 테이블을 생성하고  데이터를 확인하세요.

CREATE TABLE EMP10 AS SELECT EMPNO, ENAME FROM EMP;

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME
1	7369	SMITH
2	7499	ALLEN
3	...	...
14	7934	MILLER

실습

EMP10 테이블에서 컬럼순서상 뒷부분에  NOT NULL 제약조건을 가지는  tel(varchar2(20)) 컬럼을 추가하세요.

ALTER TABLE EMP10 ADD (tel VARCHAR2(20) NOT NULL);

<실행결과>

ORA-01758: 테이블은 필수 열을 추가하기 위해 (NOT NULL) 비어 있어야 합니다.

EMP10 테이블에는 이미 데이터행이 있으므로 NOT NULL 제약조건을 가지면서 컬럼 추가는 불가능합니다. 

실습

기존 테이블에 데이터 값이 있더라고 NULL을 허용하는 컬럼 추가는 가능 합니다.

EMP10 테이블에 NUUL값을 허용하는 tel varchar2(20) 컬럼을 추가한 후 데이터를 확인하세요.

ALTER TABLE EMP10 ADD (tel VARCHAR2(20) );

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME	TEL
1	7369	SMITH	(null)
2	7499	ALLEN	(null)
3	...	...	...
14	7934	MILLER	(null)

tel 컬럼은 EMP 테이블에서 컬럼 순서상 뒷부분에 추가  된다는 것과 기존 데이터행의 tel 컬럼은 NULL 값이 입력되어 있는 것을  확인하세요.

실습

ALTER TABLE ~ DROP COLUMN 명령으로 테이블의 컬럼을 삭제 할 수 있습니다.

위에서 추가한 tel 컬럼을 삭제하세요.

ALTER TABLE EMP10 DROP COLUMN tel;

<실행결과>

Table EMP10이(가) 변경되었습니다.

실습

NULL 허용하는 조건으로 컬럼을 추가할 때 기본값default value을 줄 수 있습니다.

tel 컬럼을 기본값을 ‘000-0000-0000’으로 주면서 추가하고 테이블 데이터를 확인하세요.

ALTER TABLE EMP10 ADD TEL VARCHAR2(20) DEFAULT '000-0000-0000';

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME	TEL
1	7369	SMITH	000-0000-0000
2	7499	ALLEN	000-0000-0000
3	...	...	...
14	7934	MILLER	000-0000-0000

기존 행에도 새로 부여한 기본값이 설정되었습니다.

실습

오라클12C 이후에는 컬럼의 순서를 변경할 수 있는 방법을 제공하는데 컬럼을 안보이게 했다가 다시 보이게 하는 방법인데요, 다시 보일때 컬럼 순서상 뒤쪽에 위치하게 됩니다.

EMP10 테이블에서 tel 컬럼을 삭제 후 컬럼 순서상 맨 앞으로 tel 컬럼을 NULL을 허용하면서 추가하여 봅시다. tel, empno, ename 순서 입니다.

ALTER TABLE EMP10 DROP COLUMN TEL;

❶ALTER TABLE EMP10 ADD (TEL VARCHAR2(20) );

❷ALTER TABLE EMP10 MODIFY (EMPNO INVISIBLE, ENAME INVISIBLE);

➌ALTER TABLE EMP10 MODIFY (EMPNO VISIBLE, ENAME VISIBLE);

SELECT * FROM EMP10;

<실행결과>

	TEL	EMPNO	ENAME
1	(null)	7369	SMITH
2	(null)	7499	ALLEN
3	...	...	...
14	(null)	7934	MILLER

❶ 에서 EMP10 테이블의 맨뒤에 tel 컬럼을 추가합니다.

❷ 앞에 있는 두개의 컬럼 empno, ename을 안보이도록 합니다. 

➌ empno, ename을 보이도록 하는데 이때 tel 컬럼보다 뒷쪽의 위치로 empno, ename 컬럼이 이동합니다. 컬럼이 INVISIBLE 상태에서 VISIBLE 상태로 변할 때 순서상 뒤쪽으로 이동하는 것 입니다.

실습

오라클12C 이전에 테이블에서 컬럼의 순서를 변경하고자 할 때는 테이블을 삭제 후 다시 원하는 순서대로 컬럼을 기술하여 만드는 방법을 사용했습니다.

오라클12C 버전 이전이라고 가정을 하고 tel 컬럼을 삭제 후  컬럼 순서상 맨 앞으로 tel 컬럼을 NULL을 허용하면서 기본값을 ‘000-0000-0000’으로 설정하여 추가해 봅시다. tel, empno, ename 순서 입니다.

ALTER TABLE EMP10 DROP COLUMN TEL;

❶ALTER TABLE EMP10 ADD TEL VARCHAR2(20) DEFAULT '000-0000-0000';

❷CREATE TABLE EMP_TEMP AS SELECT TEL, EMPNO, ENAME FROM EMP10;

➌DROP TABLE EMP10;

➍RENAME EMP_TEMP TO EMP10;

SELECT * FROM EMP10;

<실행결과>

	TEL	EMPNO	ENAME
1	(null)	7369	SMITH
2	(null)	7499	ALLEN
3	...	...	...
14	(null)	7934	MILLER

오라클12C 이전에는 컬럼의 순서를 바꿀 수 있는 방법을 제공하지 않으므로 수작업으로 이와 유사한 결과가 나오도록 아래처럼 작업을 합니다.

❶ 에서 EMP10 테이블의 맨뒤에 tel 컬럼을 추가합니다.

❷ EMP10 테이블에서 tel. empno, ename 컬럼순서로 모든 데이터를 복사하여 EMP_TEMP 테이블을 생성 합니다.

➌ EMP10 테이블을 삭제 합니다. 만약 EMP10 테이블에 생성된 인덱스등이 있다면 같이 삭제되니 아래 ➍ 단계에서 EMP10로 이름을 변경 후 별도로 생성해 주어야 합니다.

➍ EMP_TEMP 테이블의 이름을 EMP10로 변경 합니다.

10.3.2 컬럼 변경

ALTER TABE ~ MODIFY 명령으로 테이블에 존재하는 컬럼 길이, 기본값 지정, 테이터 타입 변경할 수 있습니다.  변경 명령 형식은 아래와 같습니다.

[기본 형식]

ALTER TABLE table_name MODIFY (column_name data_type [DEFAULT expr]…)

변경 원칙은 다음과 같습니다.

1. 구조를 변경할 컬럼에 기존 데이터가 없으면 타입이나 크기 변경이 자유롭습니다

2. 컬럼에 기존 데이터가 있으면 기존 데이터 길이보다 크거나 같을 경우만 길이 변경이 가능 합니다(기존 데이터의 길이 미만으로 변경은 불가능합니다).

3. 숫자 타입은 정밀도(scale) 증가는 가능합니다. 

4. 기존 데이터가 있으면 타입 변경은 VARCHAR2, CHAR으로만 가능합니다.

5. DEFAULT값을 변경은 기존 데이터에 영향이 없으며 이후 입력되는 데이터부터 지정됩니다.

ALTER TABLE ~  MODIFY 명령으로 컬럼의 길이변경, 기본값 변경등을 기존에 실습했던 내용들과 함께 실습을 해보겠습니다.

실습

ALTER TABLE ~ MODIFY 명령으로 컬럼의 길이를 변경 할 수 있습니다. 길이를 늘이는 것은 해당 데이터 타입이 허용하는 범위에서 가능하지만 길이를 줄이는 것은 기존 데이터가 있는 경우 그 길이 아래로 줄일 수는 없습니다. 

EMP10 테이블의 ename 컬럼의 길이를 5로 변경하세요.

ALTER TABLE EMP10 MODIFY ENAME VARCHAR2(5);

<실행결과>

ORA-01441: 일부 값이 너무 커서 열 길이를 줄일 수 없음

ename 컬럼의 길이가 5보다 큰 값을 가진 행이 있어서 오류가 발생했습니다.

실습

10.3.1 마지막 실습에서 tel 컬럼을 NULL을 허용하고 기본값을 ‘000-0000-0000’으로 해서 테이블의 맨 앞쪽에 추가 했습니다. tel 컬럼은 NULL을 허용하므로 INSERT  구문의 VALUE절에서 NULL 이라고 명시적으로 값을 지정해서 입력할 수도 있습니다.

EMP10 테이블에 9999, ‘홍길동’ 사원을 입력하는데 tel 컬럼의 값을 NULL로 지정해서 입력하세요.

INSERT INTO EMP10 (TEL, EMPNO, ENAME) VALUES (NULL, 9999,'홍길동');

COMMIT;

<실행결과>

1 행 이(가) 삽입되었습니다.

값이 제대로 입력되었나 확인해봅니다.

SELECT * FROM EMP10;

<실행결과>

	TEL	EMPNO	ENAME
1	000-0000-0000	7369	SMITH
2	000-0000-0000	7499	ALLEN
3	...	...	...
14	000-0000-0000	7934	MILLER
15	(null)	9999	홍길동

실습

INSERT INTO ~ VALUES에 tel 컬럼을 기술하지 않고 입력을 하면 tel 컬럼에는 기본값이 저장됨을 확인하세요.

앞에서 추가한 행을 삭제하고, 이번에는 VALUES에 tel 컬럼을 지정하지 않고 행을 추가합시다.

DELETE FROM EMP10 WHERE EMPNO = 9999;

INSERT INTO EMP10 (EMPNO, ENAME) VALUES (9999,'홍길동');

COMMIT;

SELECT * FROM EMP10;

<실행결과>

	TEL	EMPNO	ENAME
1	000-0000-0000	7369	SMITH
2	000-0000-0000	7499	ALLEN
3	...	...	...
14	000-0000-0000	7934	MILLER
15	000-0000-0000	9999	홍길동

예상대로 EMPNO가 9999인 행에 TEL값으로 기본값인 000-0000-0000가 저장되었네요.

실습

ALTER  TABLE ~ MODIFY 명령으로 컬럼의 기본값을 변경 합니다.

이번에는tel 컬럼의 기본값을 ‘999-9999-9999’로 변경합니다. 이 변경사항은 새로 입력되는 데이터 부터 반영되어야 합니다.

ALTER TABLE EMP10 MODIFY TEL DEFAULT '999-9999-9999';

앞에서 추가한 행을 삭제하고, 새 행을 추가해 변경된 기본값이 입력되나 확인해봅시다.

DELETE FROM EMP10 WHERE empno = 9999;

INSERT INTO EMP10 (empno, ename) VALUES (9999,'홍길동');

COMMIT;

SELECT * FROM EMP10;

<실행결과>

	TEL	EMPNO	ENAME
1	000-0000-0000	7369	SMITH
2	000-0000-0000	7499	ALLEN
3	...	...	...
14	000-0000-0000	7934	MILLER
15	999-9999-9999	9999	홍길동

예상대로 새로 입력한 행에만 새 기본값 ‘999-9999-9999’가 적용되었습니다.​

 

 

 

10.3.3 컬럼 삭제

테이블의 컬럼은 ALTER TABLE~ DROP COLUMN 명령으로 삭제할 수 있습니다. 

컬럼이 삭제되면 컬럼 데이터, 해당 컬럼을 참조하는 인덱스는 즉시 삭제되지만 해당 컬럼을 참조하고 있는 뷰,  함수, 프로시저는INVALID 상태가 되지만 삭제 되지는 않습니다. 

[기본 형식]

ALTER TABLE table_name DROP COLUMN column_name

컬럼을 삭제하는 실습을 하고 삭제여부를 쿼리로 확인해 봅시다.

실습

EMP10 테이블에 추가된 tel 컬럼을 삭제하고 삭제여부를 확인하세요.

ALTER TABLE EMP10 DROP COLUMN TEL;

SELECT COLUMN_NAME 

FROM    USER_TAB_COLUMNS  

WHERE TABLE_NAME = 'EMP10';

<실행결과>

	COLUMN_NAME
1	EMPNO
2	ENAME

10.3.4 컬럼 이름 변경

ALTER TABLE ~ RENAME COLUMN 명령으로 컬럼의 이름을 바꿀 수 있습니다.

[기본 형식]

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name old_column_name : 이름을 바꾸고자 하는 컬럼 new_column_name : 새로 부여하는 이름

테이블에 컬럼을 추가하고 이름을 변경하여 봅시다.

실습

EMP10 테이블에 tel 컬럼을 가변길이 문자열 20 으로 추가하고 이름을 telephone으로 변경하세요.

ALTER TABLE EMP10 ADD tel VARCHAR2(20) ; ALTER TABLE EMP10 RENAME COLUMN tel TO telephone ;

<실행결과>

Table EMP10이(가) 변경되었습니다.

telephone으로 변경 되었는지 확인해 봅시다.

SELECT COLUMN_NAME 

FROM    USER_TAB_COLUMNS  

WHERE TABLE_NAME = 'EMP10';

<실행결과>

	COLUMN_NAME
1	TELEPHONE
2	EMPNO
3	ENAME

 

#오라클 #칼럼추가, #칼럼삭제, #ALTERTABLE, #오라클테이블, #오라클강의, #오라클강좌