Oracle 기본정리 - 외래키, 제약조건

Oracle 기본정리 - 외래키, 제약조건

--예제1-외래키 만드는 방법1-------------------------------------------------------------
--테이블생성
create table dept01
(
    deptno number(3),
    dname varchar2(20),
    loc varchar2(20),
    constraint dept01_deptno_pk primary key(deptno) --제약조건이름은 'sys_**' 로 자동생성
);
 
create table emp01
(
    empno number(3) primary key
    , ename varchar2(20)
    , deptno number(3) references dept01(deptno)
);
 
--constraint 제약조건, 해당 테이블 조회
select * from user_constraints
where upper(table_name) in(upper('emp01'),upper('dept01'));
 
--테이블 완전삭제
drop table emp01 purge;
 
 
--예제2-외래키 만드는방법 2--------------------------------------------------------------------
--테이블 생성
create table emp01
(
    empno number(3) constraint emp01_empno_pk primary key
    , ename varchar2(20)
    , deptno number(3)constraint emp01_deptno_fk references dept01(deptno) --제약조건 이름 정할수 있음
);
 
----constraint 해당 테이블 조회
select * from user_constraints
where upper(table_name) in(upper('emp01'),upper('dept01'));
 
desc dept01;
 
insert into dept01 values(10,'a','a');
insert into dept01 values(20,'b','b');
 
insert into emp01 values(1,'hong',10);
insert into emp01 values(2,'kim',null);
insert into emp01 values(3,'park',40);
 
--테이블 완전삭제
drop table emp01 purge;
 
 
--예제3--외래키 만드는 방법3---------------------------------------------------
--테이블생성
create table emp01
(
    empno number(3)
    , ename varchar2(20)
    , deptno number(3)
    ,constraint emp01_empno_pk primary key(empno)
    ,constraint emp01_deptno_fk foreign key(deptno) references dept01(deptno) --제약조건이름을 지정가능, 맨뒤로 빼내서 따로 지정가능
);
 
--constraint 제약조건, 해당 테이블 조회
select * from user_constraints
where upper(table_name) in(upper('emp01'),upper('dept01'));
 
-----------------------------------------------------
--예제4
create table student
(
    iphakyear number(5) not null
    ,dept number(2) not null
    ,no number(2) not null
    ,name varchar2(10)
    ,addr varchar(10)
    ,constraint student_ip_dept_no primary key(iphakyear,dept,no)
);
 
create table jumsu
(
    no number(3) constraint jumsu_no_pk primary key
    , subject varchar2(20)
    , y       number(4)
    , d       number(4)
    , n       number(2)
    , constraint jumsu_y_d_n_fk foreign key(y,d,n) references student(iphakyear,dept,no)
);
 
--constraint 제약조건, 해당 테이블 조회
select * from user_constraints
where upper(table_name) in(upper('student'),upper('jumsu'));
 
 
------------------------------------------------------------------------------------------------------------------------
--문제1-테이블 만들어보기
/*
name : user_emp
emp_id primary key 1, 2, 3
name
email
manager_id foreign key => 1,2,3 null
job_id foreign key a b c null

name : user_job
 job_id primary key
 job_title
*/
 
--문제1(답)
CREATE TABLE user_emp
(
    emp_id number(2) constraint user_emp_emp_id_pk primary key
    , name varchar2(10)
    , email varchar2(10)
    , manager_id number(2) constraint user_emp_manager_id_fk REFERENCES user_emp(emp_id)
    , job_id varchar2(3) constraint user_emp_job_id_fk references user_job(job_id)
);
 
create table user_job
(
    job_id varchar2(3) constraint user_job_job_id_pk primary key
    , job_title varchar2(10)
);
 
--constraint 제약조건, 해당 테이블 조회
select * from user_constraints
where upper(table_name) in(upper('user_emp'),upper('user_job'));
 
--테이블 값 입력
insert into user_job values('a','a');
insert into user_job values('b','b');
insert into user_emp values(1,'hong','aa@aa.com',null,'a');
insert into user_emp values(2,'park','aa2@aa.com',null,'a');
 
--자식튜플 삭제가능
delete from user_emp
where emp_id=2;
 
--부모튜플 삭제 불가능
delete from user_job
where job_id='a';
 
--제약조건 삭제
alter table user_job
drop constraint user_job_job_id_pk cascade; -- cascade는 user_job의 job_id와 연계된 모든 제약 삭제
 
desc user_emp;
desc user_job;
 
--제약조건 2개 다시 생성
alter table user_job
 add constraint user_job_job_id_pk primary key(job_id);
 
alter table user_job
 add constraint user_emp_job_id_fk foreign key(job_id) references user_job(job_id);-- on delete cascade;
                                                                                   --cascade 제약조건을 붙일수 있음
 
--제약조건 삭제
alter table user_job
drop constraint USER_EMP_JOB_ID_FK;
 
update user_job
set job_id='t'
where job_id='a';
 
--정책이 있을경우 삭제가 안됨
delete from user_job
where job_id='a';
 
select * from user_job;