Oracle SQL 기본_12

ALTER RENAME : 테이블 이름 변경, 컬럼(변수) 이름 변경, 제약조건 이름 바꾸기

---

1. ALTER 명령어의 이해

​

ALTER는 DDL(Data Definition Language)에 속하는 명령어로, 테이블이나 컬럼의 이름을 바꾸거나 / 제약조건을 변경하거나 / 컬럼을 추가 또는 삭제하는 등의 다양한 테이블 변경 기능을 수행합니다.

​

ALTER 명령어를 용도별로 세분화하면 다음과 같습니다.

 

ALTER 명령어 유형	기능
ALTER RENAME	이름 변경	테이블 이름, 컬럼 이름, 제약조건 이름 변경
ALTER ADD	추가	테이블에 새로운 컬럼 추가, 새로운 제약조건 추가
ALTER MODIFY	유형 변경	테이블 컬럼의 데이터 유형(또는 크기) 변경
ALTER DROP	삭제	테이블에서 컬럼 삭제, 제약조건 삭제
ALTER READ	읽기/쓰기	읽기 전용 테이블로 변경 / 읽고 쓰기 가능한 테이블로 변경

 

 

2. ALTER RENAME의 용도와 표현

​

 

1) ALTER RENAME의 용도

​

ALTER RENAME은 테이블의 이름을 수정/변경하거나, 컬럼의 이름을 수정/변경하거나, 제약조건의 이름을 수정/변경합니다.

 

 

2) ALTER RENAME의 표현

​

 

(1) 기본식 - 테이블 이름 수정 :

테이블 이름을 A에서 B로 변경

alter table 테이블이름A
      rename to 테이블이름B;

​

(2) 기본식 - 컬럼 이름 수정 :

테이블A의 컬럼 이름을 K에서 L로 변경

alter table 테이블이름A
      rename column 컬럼이름K to 컬럼이름L;

 

(3) 기본식 - 제약조건 이름 수정 :

테이블A가 가진 제약조건X의 이름을 Y로 변경

alter table 테이블이름A
      rename constraints 제약조건이름X to 제약조건이름Y;

 

 

* 이하의 예제에서는 hr 연습계정의 employees 테이블을 복사한 employees_t 테이블을 사용합니다.

먼저, CREATE SELECT 구문을 사용하여 employees_t 테이블을 생성해 줍니다.

create table employees_t
as select * from employees;

 

 

3. 예제 : ALTER RENAME의 사용

- 테이블 이름, 제약조건 이름, 컬럼 이름 바꾸기

​

1) 예제 : 테이블 이름 변경

employees_t 테이블의 이름을 employees_r로 바꾸기

alter table employees_t 
      rename to employees_r;

위 SQL 문장을 실행(Ctrl+Enter)하면, 스크립트 출력 창에 '테이블 employee_t가 변경되었습니다' 메시지가 나타납니다.

 

* 이 때, 변경하려는 테이블 이름과 같은 이름의 테이블이 이미 존재하는 경우에는 테이블 이름이 변경되지 않습니다. 기존 테이블들의 이름과 중복되지 않는 다른 이름으로 바꾸어 주세요.

 

테이블의 이름이 employees_r로 변경되었으므로, SELECT FROM 구문으로도 employees_r을 조회하면 결과가 나타나는 것을 확인할 수 있습니다.

 

 

2) 예제 : 컬럼 이름 변경

employees_r(*employees_t) 테이블에서

first_name 컬럼의 이름을 'namenamename'으로 바꾸기

alter table employees_r 
      rename column first_name to namenamename;

위 '예제1)'에서의 employees_r 테이블에서 first_name 컬럼의 이름을 바꾸어 주는 것입니다.
이 또한, 다른 컬럼과 이름이 중복되지 않도록 유의해야 합니다.

 

 

3) 예제 : 제약조건 이름 변경

employees_r(*employees_t) 테이블에서

job_id 변수관련 제약조건의 이름을 'nn_job'으로 바꾸기

 

GUI로 employees_r 테이블의 제약조건을 목록으로 조회할 수 있습니다.

​

- CONSTRAINT_NAME : 제약조건 이름

- CONSTRAINT_TYPE : 제약조건 유형

- SEARCH_CONDITION : 제약조건의 체크 조건

​

job_id와 관련된 제약조건의 이름은 SYS_C0011660입니다. 이 제약조건의 이름을 'nn_job'으로 바꾸어 주는 SQL 문장은 다음과 같습니다.

 

alter table employees_r 
      rename constraints SYS_C0011660 to nn_job;

위 문장을 실행(Ctrl+Enter)하면, 제약조건의 이름이 변경됩니다.

 

 

다시 GUI를 통해 employees_r 테이블의 제약조건 리스트를 조회하였습니다.
'NN_JOB' 제약조건으로 job_id 관련 제약조건의 이름이 변경되어 있습니다.

 

 

ALTER ADD : 테이블에 새로운 컬럼 추가하기, 제약조건 추가하기

---

1. ALTER 명령어의 이해

​

ALTER는 DDL(Data Definition Language)에 속하는 명령어로, 테이블이나 컬럼의 이름을 바꾸거나 / 제약조건을 변경하거나 / 컬럼을 추가 또는 삭제하는 등의 다양한 테이블 변경 기능을 수행합니다.

​

ALTER 명령어를 용도별로 세분화하면 다음과 같습니다.

ALTER 명령어 유형	기능
ALTER RENAME	이름 변경	테이블 이름, 컬럼 이름, 제약조건 이름 변경
ALTER ADD	추가	테이블에 새로운 컬럼 추가, 새로운 제약조건 추가
ALTER MODIFY	유형 변경	테이블 컬럼의 데이터 유형(또는 크기) 변경
ALTER DROP	삭제	테이블에서 컬럼 삭제, 제약조건 삭제
ALTER READ	읽기/쓰기	읽기 전용 테이블로 변경 / 읽고 쓰기 가능한 테이블로 변경

 

2. ALTER ADD의 용도와 표현

​

1) ALTER ADD의 용도

​

ALTER ADD는 테이블에 새로운 컬럼을 추가하거나, 새로운 제약조건을 추가합니다.

​

​

2) ALTER ADD의 표현

​

(1) 기본식 : 새로운 컬럼 추가

테이블A에 새로운 컬럼X, 컬럼Y, ...를 추가하기

alter table 테이블이름A
      add 컬럼이름X 데이터유형
      add 컬럼이름Y 데이터유형
      ... ;

 

(2) 기본식 : 새로운 제약조건 추가

테이블A의 컬럼X에 새로운 제약조건C 추가하기

alter table 테이블이름A
      add constraints 제약조건이름C 제약조건유형(컬럼이름X);

 

ⓐ 제약조건 유형 : PRIMARY KEY인 경우

alter table 테이블이름A
      add constraints 제약조건이름C primary key 컬럼이름X;

 

ⓑ 제약조건 유형 : UNIQUE 인 경우

alter table 테이블이름A
      add constraints 제약조건이름C unique 컬럼이름X;

 

ⓒ 제약조건 유형 : NOT NULL인 경우

alter table 테이블이름A
      add constraints 제약조건이름C not null 컬럼이름X;

 

ⓓ 제약조건 유형 : CHECK인 경우

alter table 테이블이름A
      add constraints 제약조건이름C check(컬럼이름X가 포함된 조건);

 

ⓔ 제약조건 유형 : FOREIGN KEY 인 경우

- 테이블B의 컬럼Y를 참조하여 테이블A의 컬럼X를 구성해야 하는 경우

alter table 테이블이름A
      add constraints 제약조건이름C foreign key 컬럼이름X references 테이블이름B(컬럼이름Y);

 

* 이하의 예제에서는, scott 연습계정의 emp 테이블을 복사한 emp_test 테이블을 사용합니다.

다음의 CREATE SELECT 구문을 실행하여, emp_test 테이블을 생성해 주세요.

 

create table emp_test
as select * from emp;

 

 

2. 예제 : ALTER ADD 사용

​

1) 예제 : 테이블에 컬럼 추가

emp_test 테이블에 intereset 라는 문자열 컬럼과 zipcode라는 숫자 컬럼을 추가하기

 

alter table emp_test
add (interest varchar2(30))
add (zipcode number(5,0));

문자열 타입의 interest 컬럼, 숫자 타입의 zipcode 컬럼을 추가하는 SQL 문장입니다.

위 식을 실행(Ctrl+Enter)합니다.

 

 

emp_test 테이블에 interest, zipcode 컬럼이 추가된 모습을 볼 수 있습니다.

​

2) 예제 : 테이블에 제약조건 추가

'예제1)'에서 추가한 interest 컬럼에 중복 비허용, 문자열 길이 5 이상인 데이터만 입력할 수 있도록 제약조건 추가하기

​

interest 컬럼에 unique, 그리고 check 제약조건을 부여해야 합니다.

문자열의 길이를 나타내는 함수는 LENGTH 입니다.

이를 바탕으로 SQL 문장을 구성하면 다음과 같습니다.

 

alter table emp_test
add constraints uq_interest unique (interest);

alter table emp_test
add constraints lgt_interest check (length(interest)>=5);

각각 interest 컬럼에 unique, check 제약조건을 부여하는 코드입니다.

위 문장 2개를 각각 실행하고, GUI에서 emp_test 테이블의 제약조건 목록을 조회하면 다음과 같이 나타나게 됩니다.

 

lgt_interest, uq_interest 제약조건이 정상적으로 추가된 모습입니다.

 

 

ALTER MODIFY : 비어 있는 컬럼의 데이터 유형(타입, 크기) 변경하기, 컬럼에 할당된 데이터 크기 변경하기

---

1. ALTER 명령어의 이해

​

ALTER는 DDL(Data Definition Language)에 속하는 명령어로, 테이블이나 컬럼의 이름을 바꾸거나 / 제약조건을 변경하거나 / 컬럼을 추가 또는 삭제하는 등의 다양한 테이블 변경 기능을 수행합니다.

​

ALTER 명령어를 용도별로 세분화하면 다음과 같습니다.

ALTER 명령어 유형	기능
ALTER RENAME	이름 변경	테이블 이름, 컬럼 이름, 제약조건 이름 변경
ALTER ADD	추가	테이블에 새로운 컬럼 추가, 새로운 제약조건 추가
ALTER MODIFY	유형 변경	테이블 컬럼의 데이터 유형(또는 크기) 변경
ALTER DROP	삭제	테이블에서 컬럼 삭제, 제약조건 삭제
ALTER READ	읽기/쓰기	읽기 전용 테이블로 변경 / 읽고 쓰기 가능한 테이블로 변경

 

 

2. ALTER MODIFY의 이해와 표현

​

ALTER MODIFY는 테이블에 있는 비어있는 컬럼의 데이터 유형과 크기제한을 변경시켜 줍니다.

​

- 데이터 유형 변경 : 값이 없이 비어있는 컬럼만 가능

- 컬럼 데이터 크기 확대 : 가능

- 컬럼 데이터 크기 축소 : 컬럼이 보유한 값의 크기보다 작아질 경우 오류 발생

​

* 데이터 유형을 변경할 경우, 이미 컬럼에 포함되어 있는 값들이 없어야 합니다.

즉, 데이터 유형을 바꾸려는 컬럼은 값이 없는 상태로 비어 있어야 합니다.

만약 값이 존재하는(비어있지 않은) 컬럼의 데이터 유형을 바꾸려고 하는 경우 오류(ORA-01439)가 발생합니다.

​

* 컬럼의 데이터 크기를 축소할 경우, 컬럼이 보유하고 있는 값의 크기보다 작아지면 오류(ORA-01441)이 발생합니다.

​

테이블A에 속한 비어있는 컬럼X의 데이터 유형을 변경시키는 SQL 문장의 기본 표현은 다음과 같습니다.

 

alter table 테이블이름A modify 컬럼이름X 데이터타입;

 

 

* 이하의 예제에서는, scott 연습계정의 emp 테이블을 복사한 emp_test 테이블을 사용합니다.

다음의 CREATE SELECT 구문을 실행하여, emp_test 테이블을 생성해 주세요.

 

create table emp_test
as select * from emp;

* 만약 이미 emp_test 테이블을 갖고 있다면 / 테이블 이름이 중복된다면, 위 구문에서 emp_test를 중복되지 않는 다른 테이블 이름으로 바꾸어 주면 됩니다.

 

 

2. 예제 : ALTER MODIFY의 사용

​

1) 오류 예제 : 값이 존재하는 컬럼의 데이터 유형을 바꾸려고 시도할 경우

emp_test 테이블에서 empno, ename의 데이터유형과 크기를 바꾸기

 

GUI로 emp_test 테이블의 변수별 데이터 유형을 조회하면 위 화면과 같습니다.

​

ename 컬럼은 varchar2(30), empno 컬럼은 varchar2(10)으로 변경을 시도합니다.

 

alter table emp_test
modify ename varchar2(30)
modify empno varchar2(10);

위 SQL 문장을 실행하면, 오류가 발생합니다.

오류 보고 -

ORA-01439: 데이터 유형을 변경할 열은 비어 있어야 합니다

01439. 00000 - "column to be modified must be empty to change datatype"

*Cause:

*Action:

​

왜냐하면, empno 컬럼과 ename 컬럼은 비어있는 컬럼이 아니라 값이 포함되어 있는 컬럼이기 때문입니다.

따라서, 현재의 상태로는 데이터 유형을 변경할 수 없습니다.

 

 

2) 오류 예제 : 컬럼이 가진 값보다 더 작게 컬럼의 데이터 크기를 변경하는 경우

ename 컬럼의 데이터 크기를 3 byte로 줄여 보기

 

alter table emp_test
modify ename varchar(3);

이 경우에도 위 SQL 문장을 실행하면 오류가 발생합니다.

오류 보고 -

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

01441. 00000 - "cannot decrease column length because some value is too big"

*Cause:

*Action:

​

ename 컬럼이 이미 가지고 있는 값들 중, 3byte 보다 더 긴 값이 존재하기 때문입니다.

 

 

3) 예제 : 비어있는 컬럼의 데이터유형 바꾸기

​

먼저, ALTER ADD를 사용하여, emp_test 컬럼에 비어있는 임의의 컬럼(cinder)을 1개 추가하여 줍니다.

alter table emp_test 
add (cinder varchar(20));

emp_test 테이블에 cinder 컬럼(문자열)이 추가되었습니다. 이 컬럼은 방금 만들어졌기 때문에 어떤 값도 포함되어있지 않고 비어 있는 컬럼입니다.

​

이 비어있는 상태의 컬럼은 데이터유형을 변경할 수 있습니다.

현재 문자열(VARCHAR2) 타입이지만, 숫자 타입으로 해당 컬럼을 바꾸어 보겠습니다.

 

alter table emp_test
modify cinder number(8,0);

cinder 컬럼의 데이터유형을 변경하는 위 SQL 문장을 실행합니다. 이번에는 오류가 발생하지 않습니다.

 

GUI로 emp_test 테이블을 조회하였습니다. cinder 컬럼의 데이터 유형(DATA_TYPE)이 number(8,0)로 변경되었음을 확인할 수 있습니다.

 

 

ALTER DROP : 테이블에서 컬럼 삭제하기, 제약조건 삭제하기

---

ALTER DROP은 테이블의 컬럼 또는 제약조건을 삭제하는 역할을 수행합니다.

 

1. ALTER 명령어의 이해

​

ALTER는 DDL(Data Definition Language)에 속하는 명령어로, 테이블이나 컬럼의 이름을 바꾸거나 / 제약조건을 변경하거나 / 컬럼을 추가 또는 삭제하는 등의 다양한 테이블 변경 기능을 수행합니다.

​

ALTER 명령어를 용도별로 세분화하면 다음과 같습니다.

ALTER 명령어 유형	기능
ALTER RENAME	이름 변경	테이블 이름, 컬럼 이름, 제약조건 이름 변경
ALTER ADD	추가	테이블에 새로운 컬럼 추가, 새로운 제약조건 추가
ALTER MODIFY	유형 변경	테이블 컬럼의 데이터 유형(또는 크기) 변경
ALTER DROP	삭제	테이블에서 컬럼 삭제, 제약조건 삭제
ALTER READ	읽기/쓰기	읽기 전용 테이블로 변경 / 읽고 쓰기 가능한 테이블로 변경

 

 

2. ALTER DROP의 이해와 표현

​

ALTER DROP은 테이블에서 지정한 컬럼 또는 제약조건을 삭제해 줍니다.

​

(1) 기본식 : 컬럼 삭제

- 테이블A에서 컬럼X를 삭제

alter table 테이블이름A 
      drop column 컬럼이름X;

 

(2) 기본식 : 제약조건 삭제

- 테이블A에서 제약조건K를 삭제

alter table 테이블이름A
      drop constraint 제약조건이름K;

 

* 이하의 예제에서는, hr 연습계정의 employees 테이블을 복사한 employee_k 테이블을 사용합니다.

다음의 CREATE SELECT 구문을 실행하여, employee_k 테이블을 생성해 주세요.

 

create table employee_k
as select * from employees;

 

employee_k 테이블은 employees 테이블과 같은 구조와 레코드를 갖게 됩니다.

 

 

3. 예제 : ALTER DROP의 사용

​

1) 예제 : 컬럼 삭제

employee_k 테이블에서 last_name 컬럼을 삭제하기

 

alter table employee_k
drop column last_name;

SELECT FROM 구문으로 employee_k 테이블의 전체 컬럼과 레코드를 조회하면, last_name 컬럼이 완전히 사라진 것을 확인할 수 있습니다.

 

 

2) 예제 : 제약조건 삭제

emp_test 테이블의 아무 제약조건을 삭제하기

 

오라클 SQL 디벨로퍼의 GUI를 사용하여, employee_k 테이블이 가진 제약조건을 확인해 보았습니다. 제약조건을 삭제하기 위해서는 제약조건의 이름(CONSTRAINT_NAME)이 필요하기 때문입니다.

 

alter table employee_k
      drop constraint SYS_C0011670;

세 번째 제약조건(SYS_C0011670)을 삭제하기로 합니다.

​위 문장을 실행(Ctrl+Enter)하면, 하단의 스크립트 출력창에는 테이블이 변경되었다는 요지의 메시지가 나타나게 됩니다.

 

다시 GUI를 통해 employee_k테이블의 제약조건 리스트를 확인합니다.

​SYS_C0011670 제약조건이 삭제되어 나머지 2개의 제약조건만 남아있는 상태입니다.

 

 

ALTER READ : 테이블을 읽기 전용으로 전환 or 읽기·쓰기 가능한 상태로 바꾸기

---

1. ALTER TABLE의 이해와 표현

​

ALTER READ는 테이블의 상태를 읽기 전용 / 읽기·쓰기가능 상태를 서로 전환시켜 주는 역할을 합니다.

​

보통의 상태에서는, 우리가 각종 테이블들에 컬럼을 추가, 삭제, 변경하는 과정은 잠겨 있지 않습니다. 하지만 어떤 이유로 특정 테이블의 편집/변경을 원치 않는 상황에 놓일 때, 당해 테이블을 읽기 전용(READ ONLY)으로 전환시켜 둘 수 있습니다.

1) ALTER READ ONLY :

테이블 A를 읽기 전용 테이블로 변경하기

 

alter table 테이블이름A read only;

 

위 명령어로 읽기 전용으로 변경된 테이블은, 데이터 입력(INSERT), 컬럼 추가(ALTER ADD), 이름 변경(ALTER RENAME) 등의 각종 변경/추가 작업이 불가능한 "읽기 전용" 상태가 됩니다.

​

* 이 읽기 전용 상태에서는 테이블 전체를 날려 삭제해 버리는 DROP TABLE은 가능합니다.

 

 

2) ALTER READ WRITE :

읽기 전용 상태의 테이블A를 다시 읽기·쓰기 모두가 가능하게 하기

​

읽기 전용 상태의 테이블은 아래 명령문을 통해서 다시 원래의 편집 가능한 상태로 되돌릴 수 있습니다.

 

alter table 테이블이름A read write;

 

* 이하 예제는 scott 연습계정의 dept 테이블을 복사한 dept_test 테이블을 사용합니다.

다음의 CREATE SELECT 문장을 실행하여 dept_test 테이블을 생성해 주세요.

 

create table dept_test
as select * from dept;

 

2. 예제 : ALTER READ의 사용

​

1) 예제 : ALTER READ ONLY

dept_test 테이블을 읽기 전용으로 전환하기

 

alter table dept_test read only;

위 문장을 실행(Ctrl+Enter)하면, dept_test 테이블은 편집이 불가능한 읽기 모드로 변경됩니다.

 

 

2) 오류 예제 : 읽기 전용 테이블에서 변경을 시도할 경우

 

'예제1)'에서 읽기 전용 상태가 된 dept_test 테이블에 ALTER ADD, ALTER MODIFY, ALTER RENAME, ALTER DROP, INSERT 등을 시도해 봅니다. 본 예제에서는 ALTER DROP으로 컬럼 삭제를 시도하였습니다.

 

 

오류(ORA-12081)가 발생하였습니다. dept_test 테이블은 현재 읽기 전용 상태이므로, 작업 개신이 허용되지 않기 때문입니다.

​

이 테이블의 내용을 변경/추가하는 등 편집을 하기 위해서는, 다시 읽기와 쓰기가 가능한 모드로 전환시켜야 합니다.

​

​

오류 보고 -

ORA-12081: "SCOTT"."DEPT_TEST" 테이블에 작업을 갱신하는 것이 허용되지 않습니다

12081. 00000 - "update operation not allowed on table \"%s\".\"%s\""

*Cause: An attempt was made to update a read-only materialized view.

*Action: No action required. Only Oracle is allowed to update a

read-only materialized view.

 

 

3) 예제 : ALTER READ WRITE

읽기 전용 상태인 dept_test 테이블을 다시 편집 가능한 상태로 되돌리기

 

alter table dept_test read write;

테이블의 내용/구조를 바꾸고 싶다면, 읽기 전용 모드를 벗어나 다시 읽기·쓰기가 가능한 상태로 돌아와야 합니다.

​위 SQL 문장을 실행하면, 테이블은 다시 갱신이 허용되는 본래의 상태로 돌아오게 됩니다.