[Oracle] 오라클 #18, Alter 테이블 구조 변경

• 객체 생성 : create
• 객체 수정 : alter
• 객체 삭제 : drop

• 데이터 생성 : insert
• 데이터 수정 : update
• 데이터 삭제 : delete

테이블 수정하기

- 테이블의 구조(컬럼)를 수정하기

- 최대한 테이블을 수정할 일을 만들지 말 것이 중요*******************

1. 테이블 삭제 > 테이블 쿼리 수정 > 수정된 DDL로 새롭게 테이블을 생성

    +기존 데이터가 있으면 데이터를 백업 -> 테이블 삭제 -> 데이터 복구

    >> 개발(공부)중에만 사용O, 시스템 운영중에는 사용 X

    

2. alter 명령어 -> 테이블의 구조만 변경 + 데이터 그대로 유지

    :개발(공부)중에만사용 O , 운영중에는 사용 O(쉽지는 않다)

테이블 수정 > 컬럼 수정

1. 새로운 컬럼을 추가하기 : 가장 쉬움

2. 기존 컬럼의 정의를 수정하기(제약, 자료형, 컬럼명) : 어려움 - 컬럼명을 바꾸어야하는 방향은 최대한 피한다.

3. 기존 컬럼을 삭제하기 : 어려움 (중요데이터가 들어있을땐 어떡해?? 그게 다른 테이블하고 조인해야하는 컬럼이면 어떻게함?? 매우 위험한 일이긴하다.)

Alter 예제

테이블 생성

create table tblEdit(

    seq number primary key,

    data varchar2(20) not null

);

데이터 삽입

insert into tblEdit values(1,'마우스');

insert into tblEdit values(2,'키보드');

insert into tblEdit values(3,'모니터');

insert into tblEdit values(4,'모니터',0,'');

desc tblEdit; -- 테이블 구조 확인.

1.새로운 컬럼 추가하기

alter table  tblEdit add(price number(5) null);

★ add(추가될 컬럼의 정의);

    

not null 제약을 걸때

쿼리 : alter table tblEdit add(description varchar2(1000) not null); 

에러 발생 ORA-01758: table must be empty to add mandatory (NOT NULL) column

>> 설명 : 아니 not null로 4개 레코드있는 테이블에 description이라는 컬럼을 만들고싶은데 이미 4개가 있는 곳은 null 형태로 데이터가 만들어질텐데 그렇게되면 not null 컬럼으로 하나 추가할건데 null값이 들어가게 되므로 에러가 발생하는 것. => 처음 만들때 무언가 기본적인 값을 넣어주어야 한다.

쿼리 : alter table tblEdit add(description varchar2(1000) default '임시' not null); 

>> default로 어떠한 임시적인 값을 넣어준다.

방법은 두가지다.

1. null로 추가 > 모든 컬럼값을 수정(값 대입) > not null로 제약 변경.

2.. not null + default 추가 -> 원하는 값으로 컬럼을 수정(값 수정)

2. 기존 컬럼을 수정하기

자료형의 크기를 늘리기 ex) varchar2(1000) -> varchar2(2000)

[modify] 

쿼리 : alter table tblEdit modify(description varchar2(2000));

자료형의 크기를 줄이기 ex) varchar2(20) -> varchar2(10) 

쿼리 : alter table tblEdit modify(data varchar2(10));

예외 :ORA-01441: cannot decrease column length because some value is too big

★현재 삽입된 데이터의 크기보다는 작게 바꿀 수 없다. 

not null -> null

쿼리 : alter table tblEdit modify(description varchar2(2000) null);

null -> not null

쿼리 : alter table tblEdit add(etc varchar2(100));

 - etc는 모두 null값이 들어있는 상황...

쿼리 : alter table tblEdit modify(etc varchar2(100) not null); 

에러 발생--ORA-02296: cannot enable (HR.) - null values found

>> 당연한 상황, null 들어있는 컬럼을 not null 로 바꾸었으니 null이었던 값을 바꿔주고 나서 해야함.

쿼리 : update tblEdit set etc = '임시데이터' where etc is null;

컬럼의 타입 변경하기(이 상황을 만들지 말것!!!)

쿼리 : alter table tblEdit modify(price varchar2(5));

alter table tblEdit modify(description number);--말도안되는일. 문자를 숫자로 바꾸는것.

3. 컬럼 삭제하기

- FK에 참조되는 PK는 삭제하면 안된다.

- 복구 불가능

쿼리 : alter table tblEdit drop column etc;

4. 제약사항 추가하기/수정하기/삭제하기

not null 제외(컬럼 수정을 통해서)

alter table tblEdit add constraint tblEdit_data_uq unique(data);

 --add (data varchar2(30) unique null)

    

alter table tblEdit add constraint tblEdit_seq_pk primary key(seq);

    

alter table tblEdit add constraint tblEdit_price_ck check (price between 0 and 1000000);

alter table tblEdit add constraint tblParent_tblEdit_seq foreign key tblParent(seq);

(뭐 이런식으로 

select * from tblEdit;

desc tblEdit;

create table tblParent

(

    seq number,

    data varchar2(100) not null,

    constraint tblParent_seq_pk primary key(seq)

);

create table tblChild

(

    seq number,

    data varchar2(100) not null

);

alter table tblChild

    add constraint tblChild_seq_pk primary key(seq)

    add (pseq number)

    add constraint tblChild_tblParent_pseq_fk foreign key (pseq) references tblParent(seq);

alter table tblChild

    add constraint tblChild_seq_pk primary key(seq);

    

alter table tblChild

    add (pseq number);

    

alter table tblChild

    add constraint tblChild_tblParent_pseq_fk foreign key (pseq) references tblParent(seq);

drop table tblChild;

5. 테이블의 모든 행 삭제하기

- 테이블 초기화(구조는 그대로 두고 데이터만 리셋)

- 개발 -> 테스트 -> 완료 -> 초기화

1. drop -> create

- 은근 사용빈도 있음

- 관계에 있는 테이블들 주의!!!

- 되돌리기가 불가능(복구 불가)-위험

2. delete

- 업무상 특정 레코드 삭제

- 정석

- 모든 행 or 일부 행

- 되돌리기 가능(복구 가능)

3. truncate

- 테이블 초기화 목적

- 정석

- 테이블의 모든 행을 삭제하는 명령어(delete + 조건절 없이 실행하는 것과 동일

- 되돌리기 불가능(복구 불가능)

- 자바 : 컬렉션.clear()

create table 영업부

as

select * from tblInsa where buseo='영업부';

desc 영업부;

select * from 영업부;

--영업부 테이블 초기화

commit;

rollback;

--2, rollback 하면 언제든지 되돌릴 수 있다.

delete from 영업부;

--3. truncate : rollback해도 복구할 수 없다.(영구 불가)

truncate table 영업부;