Oracle SQL : 제약조건

제약조건.sql.html

0.01MB

SQL 제약조건 문

 

dept01 테이블 생성 

drop table dept01;

create table dept01 (
    deptno number(2) constraint dept01_deptno_pk primary key,
    dname varchar2(14),
    loc varchar2(13) );

select * from USER_CONSTRAINTS where table_name='DEPT01';

INSERT INTO dept01 values(10, '경리부', '서울');
INSERT INTO dept01 values(20, '인사부', '인천');

select * from dept01;

테이블 확인 

 

테이블 emp01 생성

drop table emp01;

create table emp01(
    empno number(4),
    ename varchar2(10) constraint emp01_ename_nn NOT NULL,
    job varchar2(9),
    deptno number(2),
    constraint emp01_empno_pk primary key (empno),
     constraint emp01_job_uk unique (job),
     constraint emp01_deptno_FK foreign key (deptno)
                references dept01 (deptno) -- dept01 테이블의 dpetno를 참조하는 포린키
     );

select * from USER_CONSTRAINTS where TABLE_NAME='EMP01';

 

insert into emp01 values (1000,'허준','사원',10);

 

select * from emp01;

-- insert into emp01 values (1001,'이순신','대리',50);
-- 참조(deptno) dept01테이블에 deptno 10하고 20밖에 없는데 50을 넣으니 오류 발생.
-- delete from dept01 where deptno=10;
-- 오류 발생 : child record found, 자식 emp01에서 deptno 10을 가지고 있기 때문임.

-- 오류해결법 : 1) 제약조건을 없애기, 2) 비활성화하기
-- 제약 조건을 비활성화하기 DISABLE CONSTRAINT
-- 제약 조건을 활성화하기 ENABLE CONSTRAINT

-- 자식 테이블의 외래키를 비활성화 하기
alter table emp01
disable constraint emp01_deptno_FK;

alter table emp01
enable constraint emp01_deptno_FK;
-- 외래키 활성화
-- 비활성화 후 deptno=10을 삭제시키고 다시 활성화 시키니까 에러발생 ,

-- 외래키 활성화

delete from dept01 where deptno=10;

-- 비활성화 후 deptno=10을 삭제시키고 다시 활성화 시키니까 에러발생

-- reason : dpetno =  10이 없음 

delete from dept01 where deptno=10;
insert into dept01 values (10, '경리부','서울');
-- 추가 후 다시 외래키 활성화

alter table emp01
enable constraint emp01_deptno_FK;

자식 테이블 외래키 활성화 

 

select * from USER_CONSTRAINTS where TABLE_NAME='EMP01' or TABLE_NAME='DEPT01';

 

 

-- 부모 테이블의 기본키를 비활성화
alter table dept01 disable constraint dept01_deptno_pk;
 -- 에러 발생
-

alter table emp01
disable constraint emp01_deptno_FK;


-- 부모테이블의 기본키를 활성화하기
alter table dept01 enable constraint dept01_deptno_pk;
-- 자식 테이블의 외래키를 활성화 하기
alter table emp01
enable constraint emp01_deptno_FK;

부모테이블의 기본키를 비활성화부터 하면 에러가 발생한다

- 해결 :
-- 방법 1) 비활성화 할 때는 자식 테이블 비활성화 하고 ->> 부모 테이블 비활성화해야함
--  방법 2) 부모테이블 비활성화 cascade 사용

-- 부모테이블의 기본키를 비활성화하기 (한꺼번에 하기 cascade)
alter table dept01 disable constraint dept01_deptno_pk cascade;

-- 부모, 자식도 모두 비활성화하는 방법은 자식 -> 부모 비활성화 해도 되지만
부모테이블의 기본키를 cascade를 사용하면 자식테이블도 비활성화 할 수 있다.

-- 반대로 활성화 할 때 부모 기본키 활성화 후 자식 외래키 활성화 

 

-- 부모테이블의 기본키를 활성화하기 (순서 1)
alter table dept01 enable constraint dept01_deptno_pk;
-- 자식 테이블의 외래키를 활성화 하기 (2)
alter table emp01
enable constraint emp01_deptno_FK;