오라클 Constraint(제약조건) 종류, 사용, 관리

-- 테이블 생성 시에 제약 조건 이름 지정하여 제약 조건 지정하기

create table new_emp1

(no number(4)

    constraint emp1_no_pk primary key,

name varchar2(20)

    constraint emp1_name_nn not null,

jumin varchar2(13)

    constraint emp1_jumin_nn not null

    constraint emp1_jumin_uk unique,

loc_code number(1)

    constraint emp1_area_ck check(loc_code < 5),

deptno varchar2(6)

    constraint emp1_deptno_fk references dept2(dcode)

); 

-- 테이블 생성 시에 제약 조건 이름 없이 제약 조건 지정하기

create table new_emp2

( no number(4) primary key,

name varchar2(20) not null,

jumin varchar2(13) not null unique,

loc_code number(1) check (loc_code < 5),

deptno varchar2(6) references dept2(dcode)

);

-- 테이블 생성 후에 제약조건 추가하기

alter table new_emp2

add constraint emp2_name_uk unique(name);

-- 오류 발생

alter table new_emp2

add constraint emp2_loccode_nn not null(loc_code);

-- null 과 not null 변경은 modify 키워드를 사용해야한다

alter table new_emp2

modify (loc_code constraint emp2_loccode_nn not null);

-- new_emp2 테이블의 no 컬럼이 emp2 테이블의 empno 컬럽값을  참조하도록 참조키 제약조건 설정

alter table new_emp2

add constraint emp2_no_fk foreign key(no)

references emp2(empno);

-- 참조하려는 부모테이블의 키가 primary key 이거나 unique key 이어야 참조키 제약조건 설정가능 

alter table new_emp2

add constraint emp2_name_fk foreign key(name)

references emp2(name);

-- emp2 테이블의 name컬럼에 unique 제약조건 설정

alter table emp2

add constraint emp2_name_uk2 unique(name);

-- 위에서 emp2 테이블의 name컬럼에 unique 제약조건을 추가해줬기 때문에 참조키 제약조건 설정 가능

alter table new_emp2

add constraint emp2_name_fk foreign key(name)

references emp2(name);

-----------------------------------------------------

-- on delete cascade 테스트

create table c_test1 (

    no number, 

    name varchar2(6),

    deptno number);

create table c_test2 (

    no number,

    name varchar2(10));

-- 오류

alter table c_test1

add constraint ctest1_deptno_fk foreign key(deptno)

references c_test2(no);

-- unique 설정

alter table c_test2

add constraint ctest2_no_uk unique(no);

-- 참조키 제약조건 설정

alter table c_test1

add constraint ctest1_deptno_fk foreign key(deptno)

references c_test2(no)

on delete cascade;

-- 데이터 삽입

insert into c_test2 values (10, 'AAAA');

insert into c_test2 values (20, 'BBBB');

insert into c_test2 values (30, 'CCCC');

commit;

select * from c_test2;

insert into c_test1 values (1, 'apple', 10);

insert into c_test1 values (2, 'banana', 20);

insert into c_test1 values (3, 'cherry', 30);

-- on delete cascade 테스트 수행

insert into c_test1 values (4, 'peach', 40); -- 오류발생

select * from c_test1; -- 총 3건

delete from c_test2 where no=10;  -- 부모테이블 레코드 삭제

select * from c_test1; -- 총 2건임, 자식테이블 레코드 같이 삭제됨

alter table c_test1 drop constraint ctest1_deptno_fk;

alter table c_test1

add constraint ctest1_deptno_fk foreign key(deptno)

references c_test2(no)

on delete set null;

select * from c_test1; 

select * from c_test2; 

delete from c_test2 where no=20;

select * from c_test1; -- deptno 자리가 20이었던 곳에 null로 셋팅됨

-- 오류 발생. 기존에 null 값이 있어서 변경할 수 없음

alter table c_test1

modify (deptno constraint ctest1_deptno_nn not null);

select * from c_test1;

update c_test1 set deptno=30

where no = 2;

commit;

select * from c_test1; 

alter table c_test1

modify (deptno constraint ctest1_deptno_nn not null); -- c_test1의 deptno컬럼을 not null로 제약조건 설정

select * from c_test2; // 총 1건

delete from c_test2; -- c_test1의 deptno 컬럼에 not null 제약조건이 걸려있어서 에러가 나고, 부모테이블 데이터 역시 삭제 불가

select * from c_test2; // 총 1건. 데이터 그래도 있음 

-----------------------------------------------------

-- 제약조건 관리하기

-- disable novalidate 사용하기

insert into t_novalidate values (1, 'DDD');

alter table t_novalidate

disable novalidate constraint sys_c007040;

insert into t_novalidate values (1, 'DDD');

-- disable validate 사용하기

insert into t_validate values(4, null);  -- 제약조건이 걸려있어더 null 값 입력안됨

alter table t_validate

disable validate constraint tv_name_nn;  -- not null 제약조건을 disable validate 했음

insert into t_validate values(4, null); -- 여전히 insert 안됨. disable validate 옵션은 해당 컬럼의 데이터를 변경할 수 없게 하는 옵션이다.

-- disable validate 옵션 설정 후 다른 데이터값 변경 시도, 에러남. 

-- disable validate 옵션은 테이블의 내용을 변경할 수 없도록 한다. insert, update, delete 안됨

insert into t_validate values (4, 'DDD');

insert into t_validate values (4, null);

insert into t_validate (no) values (4);