외래키 제약조건 foreign key const

foreign key const 

-define

 * create a parent table for the following explanation

create table dept(

dno   number(4) primary key

,dname varchar2(20) not null;

1) column level

create table emp(

empno number(4) primary key

,ename varche2(30) not null

, sal  number(6)

,dno   number(4) references dept(dno));

cf.) if dno os pk for dept then you can omit the column dno,.

2) table level

create table emp(

 empno number(4)

, ename varchar2(30)

, sal number(6)

, dno number(4)

, primary ket(empno)

, check(ename is not null)

/*not null 은 쓰지못함*/

, foregin key(dno) references dept(dno));

- drop

<assume: table emp refereces table dept wirh fk is emp.

dno and pk and emp and ept are populated.>

1) delete from dept

    where dno = 10;

=> 참조제약 위배 에러발생

해결: on delete cascade or on delete set null on fk

2) alter table dept

   drop column dno;

=> 참조제약 위배 에러발생

해결: alter table dept drop cloumn dno cascade constraint

3) alter table dept

   drop primary ket;

=> 참조제약 위배 에러발생

해결: alter table dept drop primary key cascade;

4) alter table dept

   disable primary key;

=> 참조제약 위배 에러발생

해결: alter table disable primary key cascade;

5) drop 부모table dept;

=> 참조제약 위배 에러발생

해결: drop table dept cascade constraint;