240304 DB - SQL활용 데이터정의어(DDL) - table3

테이블 수정(alter table)

※desc 테이블명은

제약 조건은 not null만 알 수 있음, 어느 조건이 있는지 확인하기 위해서는 db사전을 이용해야함

-- db사전 통해 제약 조건을 확인 user_constraints

desc user_constaraints;

-- constraint_name, constraint_type, table_name, search_condition(ck), r_constraint_name(fk 참조)을 많이 사용!

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

 

그림 1.

 

부모테이블명(부모컬럼명)은 직접적으로 나오지 않음. 참조하는 곳에 선언된 제약조건명

 

자주 사용하므로 저장해서 사용하기

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

 

-- employees 테이블 복사본 만들기
create table emp2024 as select * from employees;

desc emp2024;

 

그림 2.

복사본의 테이블 생성하면 테이블의 구조, 데이터는 그대로 복사되지만, 제약조건 not null만 복사됨
나머지 제약조건은 필요시 추가, 또한 제약 조건명이 자동으로 부여됨

 

• 제약 조건 추가

-- add 테이블 레벨 문법
alter table emp2024 add constraint e24_eid_pk primary key(employee_id);

-- modify 컬럼 레벨 문법
alter table emp2024 modify email constraint e24_email_uk unique;

-- 
alter table emp2024 modify department_id constraint e24_did_fk references departments(departments_id);

 

• 제약 조건 삭제

-- pk 제약조건삭제
alter table 테이블명 drop primary key [cascade];

-- pk, fk, uk, ck, nn 제약조건삭제
alter table 테이블명 drop constraint 제약조건명 [cascade];
-- 제약조건명을 알기 위해서 db사전 이용

alter table emp2024 drop constraint e24_email_uk;

alter table emp2024 drop primary key;

-- 실습을 위해서 추가 제약 조건 추가
alter table emp2024 add constraint e24_eid_pk primary key(employee_id);

alter table emp2024 add constraint e24_mid_fk foreign key (manager_id) references emp2024(employee_id);

-- 부모 e_id, 자식 m_id. 자식을 삭제는 ㄱㅊ, 부모 삭제는 오류발생
alter table emp2024 drop primary key cascade; -- 제약조건이 있다면 같이 삭제(manager_id와 employee_id가 함께 삭제됨)

 

 

• 컬럼명 수정

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

--
alter table emp2024 rename column first_name to f_name;

alter table emp2024 rename column last_name to l_name;

alter table emp2024 rename column department_id to dept_number;

 

• 제약조건명 수정

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

--
alter table emp2024
rename constraint sys_c007434(job_id 제약조건명) to e24_job_nn;

alter table emp2024
rename constraint e24_did_fk to e24_dept_fk;

 

• 테이블(object(view,index))명 수정

rename 기존테이블명to 새테이블명;

--
rename emp2024 to emp24;

 

---

 

테이블 삭제(drop table)

drop table 테이블명;

 

DDL(데이터정의어) : create, alter, drop, truncate는 auto-commit을 내포하고 있어 rollback 불가.

단 오라클의 경우 Flashback Table 기능이 존재하기 때문에 삭제된 테이블을 되살릴 수 있음.

-- 휴지통 조회
show recyclebin;
-- 또는
select original_name, opertaion, droptime
from recyclebin;

--복구
flashback table 테이블명 to before drop;

 

 flashback 했을 경우 제약조건명과 제약조건도 없어짐

-- 휴지통을 거치지 않고 테이블 삭제
drop table 테이블명 purge;

 

테이블 절단(truncate table)

테이블 구조만 남겨두고 테이블의 모든 행을 제거

truncate table 테이블명;

 

구분	delete	truncate
개념	테이블의 특정 행 삭제 (where 절 생략 시 모든 행 삭제)	테이블의 모든 행 절단(삭제)
SQL	delete from 테이블명	truncate table 테이블명
차이점	DML(데이터 조작어) ROLLBACK 가능	DDL(데이터정의어) ROLLBACK 불가

 

---