데이터정의어DDL : 테이블수정(컬럼수정, 제약조건수정, 컬럼명수정, 제약조건명수정), 테이블명변경, 테이블삭제, 테이블절단, truncate과 delete과 drop차이

테이블 수정 : 컬럼

컬럼추가

• 괄호생략가능

alter table 테이블명
add (추가할 쿼리);

• 예시 :

//잡아이디컬럼을 추가하는 예시
alter table dept80
add (job_id varchar2(9));

컬럼수정

• 괄호생략가능

• 수정가능한 데이터

  • 데이터타입 : 항상 되는 건 아니다. 현재 들어가 있는 데이터에 따라 다를 수 있다.

    • number -> varchar2 변경가능
    • number -> date 변경불가능
    • date -> varchar2 변경가능
    • date -> number 변경불가능
    • varchar2 -> number 변경불가능, , BUT 컬럼 안 모든 데이터가 number형식인 경우 변경가능.
    • varchar2 -> date 변경불가능, BUT 컬럼 안 모든 데이터가 date형식인 경우 변경가능.

  • 컬럼사이즈 : 컬럼사이즈 늘리는 건 언제나 가능한데 컬럼사이즈를 줄이는 건 에러가 발생할 수도 있다.

    • 에러 : 오류 보고 - ORA-01441: 일부 값이 너무 커서 열 길이를 줄일 수 없음. 01441. 00000 - “cannot decrease column length because some value is too big”

  • default값

  • 등등

alter table 테이블명
modify (수정할 쿼리);

• 예시 :

//컬럼사이즈를 30으로 수정
alter table dept80
modify last_name varchar(30);

컬럼삭제

• column명 생략가능 -> 생략시 삭제조건에 괄호필수

alter table 테이블명
drop column 삭제하고싶은컬럼명;
//또는
alter table 테이블명
drop (삭제하고싶은컬럼명);

• 예시 :

alter table dept80
drop COLUMN job_id;
//또는
alter table dept80
drop (job_idd);

컬럼명변경

alter table 테이블명
rename 기존컬럼명 to 새로운컬럼명;

//예시
alter table emp2
rename cloumn employee_id to emp_number;

테이블 수정 : 제약조건

기존테이블에 제약조건 추가

문법이 두가지이다.

//테이블레벨 문법 : PK, FK, UK, CK사용가능
alter table 테이블명
add constraint 고유한제약조건명 제약조건유형(컬럼명);

//컬럼레벨 문법 : PK, FK, UK, CK, NN사용가능
alter table 테이블명
modify 컬럼명 contsraint 고유한제약조건명 제약조건유형;

• 예시

//테이블레벨 문법 - primary key 추가
alter table emp2
add constraint e2_eid_pk PRIMARY KEY(employee_id);

//컬럼레벨 문법 - primary key 추가
alter table emp2
modify employee_id constraint e2_eid_pk PRIMARY KEY

//테이블레벨 문법 - Foreign Key 추가
alter table emp2
add constraint e2_did_fk foreign key(department_id)  
references departments(department_id);

//컬럼레벨 문법 -Foreign Key 추가
alter table emp2
modify department_id constraint e2_did_fk
references departments(department_id);

• 제약조건 확인하는 쿼리

select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints
where lower(table_name) = lower( '&table_name');

기존테이블의 제약조건 삭제

• 제약조건을 삭제하고싶으면 해당 제약조건명을 알고 있어야한다
• 제약조건명을 모르는 경우 위의 제약조건 확인하는 쿼리로 검색해서 알아내면됨
• primary key 경우 제약조건명을 알지못해도 제약조건 삭제 가능.
  • WHY? 테이블당 PK는 한번만 가능하기때문에. 헷갈리지않음
  • BUT, PK컬럼에 FK가 걸려있는 경우 삭제할 수 없다. 만약 FK 먼저 삭제해주면 PK삭제가능
  • cascade 옵션 : PK삭제시 걸림돌이 되는 제약조건까지 함께 삭제하겠다는 의미.

alter table 테이블명
drop constraint 제약조건명;

//primary key인경우
alter table 테이블명
drop primary key;

//PK컬럼에 FK가 걸려있는데도 삭제하고싶은경우 (캐스캐이드옵션사용)
alter table 테이블명
drop primary key cascade;

• 예시 :

alter table emp2
drop constraint e2_eid_pk

제약조건명 변경

alter table 테이블명
rename constraint 기존제약조건명 to 새로운제약조건명;

//예시
alter table emp2
rename constraint employee_id to emp_number;

테이블명 변경

alter 명령어가 아니다.

rename 테이블명 to 새로운테이블명

//예시
rename emp2 to emp3;

테이블 삭제

• 가장 신중하게 명령어입력할 것.
• DDL구문은 오토트랜잭션에 의해 rollback 안됨.
  • oracle은 휴지통에 잘못삭제한 테이블이 있으면 flashback가능.
  • oracle은 휴지통을 거치지않고 테이블삭제 가능(purge) : 보안때문에 완벽하게 테이블을 지울때 사용
  • 언제까지 휴지통에 데이터가 남아있을까?
    • 기준은 시간이 아니라 덮어쓰여지는 작업량이다.
    • 내가 삭제한 후 삭제됐던 테이블이 많이 없을경우 일년이 지나도 flashback할수있다.
    • 휴지통에 버려지면 테이블명만 자동으로 바뀐 후(object_name) 일정 데이터가 휴지통에 차면 이전에 있던 데이터가 덮어쓰여지게된다.

drop table 테이블명

//1oracle만 가능한 flashback
//1-1. 오라클 휴지통에 내가 drop한 테이블있는 지 확인
select original_name, object_name, operation, droptime
from recyclebin;

//1-2. flashback
flashback table emp3 to before drop;

//1-3. 휴지통을 거치지않고 테이블삭제 가능
drop table emp3 purge;

테이블절단 truncate table

• 테이블 구조는 남겨두고 테이블의 모든 행 제거함
• DLL구문으로 rollback안됨.
• 추가가능한 속성값없음. 딱 아래 코드 한 줄이 다름. 응용없음.

truncate table table_name;

truncate과 delete, drop 차이

• 면접질문에서 자주 물어봄
• 기본이 다르다.
  • delete의 기본 : 특정행 삭제, 단 where절 생략시 모든 행 삭제도 가능. DML이다.
  • truncate의 기본 : 모든 행 삭제, DDL이다
  • drop의 기본 : 테이블삭제, DDL이다.