[ORACLE]제약조건 변경 + ON DELETE

제약 조건 추가

primary key 

alter table emp_copy
add constraint emp_copy_eno_pk primary key(eno);

alter table dept_copy
add constraint dept_copy_dno_pk primary key(dno);

foreign key

alter table emp_copy
add constraint emp_copy_dno_fk
foreign key(dno) references dept_copy(dno);

not null로 변경

alter table emp_copy
modify ename constraint emp_copy_ename_nn not null;

check

alter table emp_copy
add constraint emp_copy_salary_ck CHECK(salary>1000);
--실패 이미 insert된 값들 중에 1000보다 작은 값들이 있기 때문에 

alter table emp_copy
add constraint emp_copy_salary_ck CHECK(500<=salary and salary<10000);

alter table dept_copy
add constraint dpet_copy_dno_ck CHECK(dno IN (10,20,30,40,50));

 

 

default-제약조건아님 주의 데이터사전에서 조회되지 않음

alter table emp_copy
modify salary default 500;

 

 

제약조건 제거

부모테이블의 기본 키 제약 조건을 제거 하려면 

foreign key 제약조건 먼저 제거 한 후 제거

CASCADE 옵션 사용

alter table dept_copy
drop primary key;

alter table dept_copy
drop primary key cascade;--참조키먼저 없애고 primary key 제약조건을 없앰

alter table emp_copy
drop constraint emp_copy_ename_nn;--not null 제약조건 제거

제약조건 활성화 비활성화

alter table emp_copy
disable constraint emp_copy_dno_fk; --비활성화

alter tabel emp_copy
enable constraint emp_copy_dno_fk; --활성화

 

참고사항 : ON DELETE 테이블 레벨에서만 가능

1. NO action(기본값) : 부모테이블 기본키 값이 자식테이블에서 참조하고 있으면 부모테이블의 행에 대한 삭제 불가
  ※restrict(MYSQL에서 기본값), MYSQL에서 restrict는 no action과 같은 의미로 사용함)
   ※오라클에서는 restrict와 no action은 약간의 차이가 있음
2. cascade : 참조되는 '부모 테이블이 기본키 값이 삭제'되면 연쇄적으로 '자식 테이블이 참조하는 값 역시 삭제'
부서테이블의 부서번호 40 삭제할 때 사원테이블의 부서번호40도 삭제하여 '참조무결성 유지함'
3. set null : 참조되는 '부모테이블의 기본키 값이 삭제'되면 해당 참조하는 '자식 테이블이 참조하는 값들이 null로 설정됨' 
   (단,null값을 허용할때 )
4. set default :자식테이블에서 미리 default값을 설정
참조되는 '부모테이블의 기본키 값이 삭제'되면 해당 참조하는 '자식 테이블이 참조하는 값들이 default로 설정됨' 
※이 제약조건이 실행하려면 모든 참조키 컬럼(즉 사원테이블의 dno)에 default값 정의가 있어야 함
컬럼이 null을 허용하고 명시적 default 값이 설정되지 않은 경우 null은 해당 컬럼의 암시적인 default 값

 

create table emp_second(
eno number(4) constraint emp_second_eno_pk primary key,
ename varchar2(10),
job varchar2(9),
salary number(7,2) default 1000 check(salary>0),
dno number(2),

constraint emp_second_dno_fk FOREIGN KEY(dno)  references department2(dno) ON DELETE CASCADE 
--참조되는 '부모 테이블이 기본키 값이 삭제'되면 연쇄적으로 '자식 테이블이 참조하는 값 역시 삭제'
--부서테이블의 부서번호 40 삭제할 때 사원테이블의 부서번호40도 삭제하여 '참조무결성 유지함'
);

위의 제약조건 제거 CASCADE와는 차이가 있으므로 유의