오라클 외래키 관련 작업

GUI로 제약조건을 확인하는 방법

 

 

WorkSheet로 확인하는 방법

 

참조 무결성 위배 데이터 입력

FOREIGN KEY (주문고객) REFERENCES 고객(고객아이디)

방법 1 : 외래키를 고려하여 입력 순서를 조정. 기본키를 가진 부모 테이블을 먼저 입력

방법 2: 기존 외래키를 삭제 후 무결성 위배 데이터를 입력한 뒤 다시 외래키 생성하는 방법

방법 3 : 기존 외래키를 비활성화한 후 무결성 위배 데이터를 입력한 뒤 다시 외래키 활성화하는 방법

 

 

<기존 외래키를 삭제 후 무결성 위배 데이터를 입력한 뒤 다시 외래키 생성하는 방법>

외래키를 고려하여 입력 순서를 조정. 기본키를 가진 부모 테이블을 먼저 입력

만약, 고객 TABLE의 데이터를 입력하지 않은 상태에서

INSERT INTO 주문 VALUES ('o01', 'apple', 'p03', 10, '서울시 마포구', '19/01/01');

====> 참조 무결성 제약조건 때문에 외래키인 주문고객과 주문제품에 값을 입력할 수 없음!

* 참고 무결성 제약조건 : FK값은 NULL이나 기본키 값이어야 한다.

고객과 제품테이블을 먼저 입력.
 외래키를 고려하여 입력 순서를 조정. 기본키를 가진 부모 테이블을 먼저 입력

 

제품에 grape라는 데이터가 없기 때문에 참조 무결성 제약조건이 발생하여 입력이 안된다.

테이블을 만들 때 

CONSTRAINT FK_고객_주문 FOREIGN KEY(주문고객)   REFERENCES   고객(고객아이디),
FOREIGN KEY(주문제품)   REFERENCES   제품(제품번호)

이런식으로 제약조건에 이름을 붙여준거고 안붙여진거는 자동적으로 만들어진 것이다.

 

ALTER TABLE 주문 drop CONSTRAINT FK_고객_주문;

를 하게 되면 

다음과 같이 제약조건이 사라졌다.

 

제약조건이 사라졌기 때문에

INSERT INTO 주문 VALUES ('o11', 'grape', 'p06', 30, '부산시 해운대구', '20/08/05');

를 해주면 다음과 같이 삽입이 잘된다.

그다음

 

ALTER TABLE 주문 
ADD CONSTRAINT FK_고객_주문 
FOREIGN KEY (주문고객) 
REFERENCES 고객 (고객아이디);

 

를 이용해서 삭제한 제약조건을 다시 넣어주려고 했으나

다음과 같이 오류가 떴다. 

이것은 아까 grape를 넣어주고 나서 다시 검사를 하는 과정에서 제약조건을 해제하고 강제적으로 넣은것이기 때문에

다시 검사를 진행하니까 오류가 났다.

 

ALTER TABLE 주문
ADD CONSTRAINT FK_고객_주문
FOREIGN KEY (주문고객)
REFERENCES 고객 (고객아이디)
    ENABLE NOVALIDATE;

이 명령어는 무결성 위배여부를 검사하지 않고 진행하겠다 라는 의미이므로 잘 수행이 되었다.

 

주문번호 o11의 주문고객은 엉터리값(무결성위배)이지만 그대로 입력되어 있는걸 볼 수 있다.

 

<기존 외래키를 비활성화한 후 무결성 위배 데이터를 입력한 뒤 다시 외래키 활성화하는 방법>

이 작업을 위해서 스키마 Reset을 해야 한다.

Reset은 세션 연결을 해제하고 system에서 사용자를 다시 만들어준 다음에

hmart에 가서 데이터 입력을 한 상태를 말한다.

 

방법 2와 다르게 

ALTER TABLE 주문 DISABLE CONSTRAINT FK_고객_주문;

를 통해 drop대신 disable를 써서 비활성화를 시켜준다.

 

ALTER TABLE 주문 ENABLE CONSTRAINT FK_고객_주문 ;  

는 다시 활성화를 시켜주는 명령어이다.

이것또한 엉터리 값이 들어가 있기 때문에 오류가 발생한다.

 

ALTER TABLE 주문 ENABLE NOVALIDATE CONSTRAINT FK_고객_주문 ;

무결성 위배 여부를 검사하지 않고 활성화 시켜주는 명령어이다.

 

 

 

외래키가 참조하는 기본키의 Delete연산

FOREIGN KEY 주문고객 REFERENCES 고객(고객아이디)
[ on delete { no action | cascade | set null } ]

 

DELETE FROM 고객 WHERE 고객이름='정소화';  

 

주문 테이블에서 참조 무결성 위배가 발생하는 경우 삭제 거부를 하였다.

 

작업 없음 : no action /  No Action(작업 없음? --> 작업거부)

고객 Table의 정소화 튜플의 삭제를 시도하면 --> 거부

 

종속 삭제 : cascade / Cascade ( 연쇄 삭제 )

만약, 고객 Table의 정소화 튜플의 삭제를 시도하면 DELETE FROM 고객 WHERE 고객이름='정소화';
주문 Table의 apple 고객의 튜플이 모두 삭제 됨

널 설정 : set null / 연관되는 외래키값을 NULL로 변경(참조 무결성 위배가 아님 - 외래키는 NULL이 될 수 있으니까!)

 

 

 

외래키가 참조하는 기본키의 Update연산

고객 Table의 PK(고객아이디)를 주문Table에서 참조(주문고객)
고객 Table의 정소화의 고객아이디를 apple1으로 변경하고자 한다.

 

이런 경우 PL SQL Programming에 Trigger로 해결한다.

 

 

Drop Table

DROP TABLE 고객;

오류 발생(Default가 No Action)

굳이 삭제하고 싶다면 : 참조하는 외래키가 있는 테이블을 먼저 삭제

 

DROP TABLE 고객 CASCADE CONSTRAINTS;

정상적으로 고객 테이블이 삭제됨
단, 삭제는 하되 연관되어 있는 제약조건도 함께 삭제됨