오라클 테이블, DELETE, UPDATE 시 참조 무결성 규칙, 외래키 데이터 삭제

 

오라클 테이블, DELETE, UPDATE 시 참조 무결성 규칙, 외래키 데이터 삭제

참조무결성(외래키) 제약조건이 걸려 있는 경우 부모, 자식 테이블에서 가능한 DML을 정리했습니다.

부모테이블에서 삭제와 업데이트가 진행될 때 참조 무결성 규칙은 다음과 같습니다.

규칙	역할
Restrict(Default)	부모테이블에서 행을 삭제 또는 수정하기 전에 자식테이블에서 부모테이블의 값을 참조하고 있으면 오류로 처리합니다.
Set to Null	부모 테이블에서 참조되는 값을 삭제나 업데이트할 때 모든 자식 테이블의 외래 키값을 NULL로 만듭니다.
Set to Default	부모 테이블에서 참조되는 값을 삭제나 업데이트할 때 모든 자식 테이블의 외래 키값을 기본값으로 만듭니다.
Cascade	부모 테이블에서 참조되는 값을 삭제할 때는 해당 값을 가지는 자식 레코드를 모두 삭제하고, 업데이트할 때는 해당 값으로 모두 변경합니다.
No Action	부모테이블에서 행을 삭제 또는 수정한 후 자식테이블에서 무결성 제약조건을 위배하지 않으면 그대로 두고 위배하면 오류로 처리 합니다. 요즘은 거의 사용하지 않습니다.

앞에서 실습한  CUSTOMER, SALES 테이블에 입력된 데이터를 그대로 두고 아래 실습을 진행합니다. 부모/자식 관계에서 부모 테이블의 데이터 삭제시 참조무결성 규칙 ON DELETE SET DEFAULT, ON DELETE SET NULL, ON DELETE CASCASE를 테스트해 보겠습니다.

실습

부모테이블에서 삭제와 업데이트가 진행될 때 참조 무결성 규칙의 기본값은 RESTRICT 입니다. 부모테이블의 값이 수정 또는 삭제될 때 자식테이블에 그 값이 내려가 있으면 오류로 처리 합니다.

CUSTOMER 테이블에서 1번 고객을 삭제합니다.

DELETE FROM CUSTOMER WHERE ID = 1;

<실행결과>

ORA-02292: 무결성 제약조건(SCOTT.SYS_C007527)이 위배되었습니다- 자식 레코드가 발견되었습니다

자식 데이터가 있는 경우 부모 데이터를 삭제할 수 없기 때문에 에러가 발생했습니다.

실습

참조무결성 삭제 규칙중 부모테이블의 데이터가 삭제되면 자식테이블의 외래키 컬럼을 NULL로 변경하는 옵션을 지정하여 테이블을 다시 생성하고 이를 테스트 해보겠습니다.

SALES 테이블을 삭제 후 다시 생성합니다. 이때 부모 테이블(CUSTOMER)의 값이 삭제될 때 자식 테이블의 해당 값을 NULL로 바꾸는 ON DELETE SET NULL을 옵션을 사용합시다.

DROP TABLE SALES; CREATE TABLE SALES (     ID NUMBER PRIMARY KEY,     CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ON DELETE SET NULL,     SALE_DATE DATE,         AMOUNT NUMBER );

<실행결과>

Table SALES이(가) 생성되었습니다.

다시 부모 테이블인 CUSTOMER 테이블에서 1번 고객을 삭제합니다.

DELETE FROM CUSTOMER WHERE ID = 1;

COMMIT;

<실행결과>

1 행 이(가) 삭제되었습니다.

이번에는 오류 없이 삭제가 됩니다.

자식테이블인 SALES 테이블의 외래키 컬럼(customer_id)의 값이 NULL로 변경된것을 확인합시다.

SELECT * FROM SALES;

<실행결과>

	ID	CUSTOMER_ID	SALE_DATE	AMOUNT
1	1	(null)	20/12/13	100000

자식테이블에서 CUSTOMER_ID 컬럼에 외래키 지정시 ON DELETE SET NULL 옵션을 사용했으므로 customer_id 컬럼의 값이 NULL로 변경됐습니다.

실습

이번에는 부모테이블 데이터가 삭제되면 자식테이블의 외래키 컬럼의 값을 기본값으로 변경하는 옵션인 ON DELETE SET DEFAULT를 사용해 보겠습니다.

SET DEFAULT절을 사용해 SALES SALES 테이블을 삭제 후 다시 생성합니다.

CREATE TABLE SALES (

    ID NUMBER PRIMARY KEY,

    CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ON DELETE SET DEFAULT,

    SALE_DATE DATE,    

    AMOUNT NUMBER

);

<실행결과>

ORA-03001: 현재에는 구현되어 있지 않은 기능입니다.

오라클19C에서 지원하지 않아 부모 테이블의 데이터가 삭제될 때 기본값로 설정하는 SET DEFAULT절을 사용할 수 없어 에러가 발생했습니다.

실습

ON DELETE CASCADE 옵션을 지정하면 부모데이터가 삭제될 때 그 자식들도 같이 삭제할 수 있습니다. CUSTOMER 테이블의 데이터를 다시 입력하고 SALES 테이블을  ON DELETE CASCADE 옵션을 기술하여 다시 생성하고 CUSTOMER 테이블의 데이터를 삭제하여 자식테이블 SALES도 같이 삭제 되는지 확인해 봅시다.

SALES 테이블을 삭제 후 다시 생성합니다. 부모 테이블의 데이터가 삭제될 때 자식 테이블도 같이 삭제되도록 설정 합시다.

INSERT INTO CUSTOMER VALUES  (1, '1길동');

DROP TABLE SALES;

CREATE TABLE SALES (

    ID NUMBER PRIMARY KEY,

    CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ON DELETE CASCADE,

    SALE_DATE DATE,    

    AMOUNT NUMBER

);

INSERT INTO SALES VALUES  (1, 1, SYSDATE, 100000);

<실행결과>

1 행 이(가) 삽입되었습니다.

Table SALES이(가) 삭제되었습니다.

Table SALES이(가) 생성되었습니다.

1 행 이(가) 삽입되었습니다.

SQL*Developerr에서 F5 키로 실습 쿼리를 한번에 실행 할 수 있습니다.

부모 테이블의 데이터를 삭제합니다.

DELETE FROM CUSTOMER WHERE ID = 1;

COMMIT;

<실행결과>

1 행 이(가) 삭제되었습니다. 

커밋 완료.

부모 데이터가 삭제될 때 자식 데이터도 같이 삭제됩니다. 진짜 그런지 확인해봅시다.

두 테이블을 SELECT로 조회해 봅시다.

SELECT COUNT(*) FROM CUSTOMER;

<실행결과>

	COUNT(*)
1	0

SELECT COUNT(*) FROM SALES;

<실행결과>

	COUNT(*)
1	0

#참조무결성, #DELETECASCADE,#오라클, #테이블, #외래키, #ORACLE, #오라클교육, #오라클강좌

오라클 테이블, 외래 키 제약조건(Foreign Key Constraints)

 

오라클 테이블, 외래 키 제약조건(Foreign Key Constraints)

외래 키foreign key는 테이블의 컬럼값이 자신 또는 다른 테이블의 기본 키와 고유 키 값만 오도록 할 때 지정하는 제약조건입니다. 따라서 외래 키로 지정할 때 참조되는 테이블 컬럼은 기본 키 또는 고유 키이어야 합니다. 물론 외래키 컬럼은 NULL을 허용할 수 있으며 이 경우 NULL 값은 입력될 수 있습니다. 즉 외래키는 값이 없는것(NULL)은 이해 하지만 값이 들어 올때는 부모 테이블(참조되는 테이블)의 기본키, 고유키 값만 들어 올 수 있다고 정의하는 것 입니다. 

사원테이블emp의 deptno 컬럼은 NULL을 허용하면서 부서테이블dept의 PK 컬럼(deptno)을 참조하는 외래키로 NULL 값은 입력 될 수 있지만 값이 들어 온다면 부서 테이블의 기본키인 deptno 값 중에서만 들어와야 한다는 것입니다. 이때 사원테이블이 자식, 부서테이블이 부모 테이블이 되며 사원테이블의 deptno 컬럼은 외래키foreign key, 부서테이블의 deptno 컬럼은 참조키referencial key라고 합니다.

외래키와 참조키가 하나의 테이블에 존재할 수 있는데 이를 자기참조(Self Reference)라고 하고 이 경우 부모 테이블과 자식 테이블은 동일합니다. 예를 들어 사원테이블에서 관리자(mgr) 컬럼은 외래 키 컬럼으로 자기자신 테이블(EMP)의 사번(empno) 컬럼을 참조합니다. 이러한 구조를 가진 경우 관리자의 이름을 알려면 관리자의 사번으로 사원 테이블을 검색해야 하므로 셀프 조인self jon이 자주 사용됩니다.

 

사용자가 작성한 USER_CONSTRAINTS 뷰를 통해 외래키 제약조건들을 조회하고 외래키를 삭제 후 다시 생성하는 실습을 해보겠습니다.

실습

USER_CONSTRAINTS 딕셔너리 뷰를 통해 사용자가 생성한 제약조건을 조회할 수 있습니다.

사원(EMP) 테이블의 제약조건을 조회 합시다.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE 

FROM   USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMP';

<실행결과>

	CONSTRAINT_NAME	CONSTRAINT_TYPE
1	FK_DEPTNO	R
2	FK_MGR	R
3	PK_EMP	P

R은 참조무결성, 외래 키를 의미하고, P는 기본 키를 의미합니다. EMP 테이블에서 deptno 컬럼은 외래키로 DEPT 테이블의 deptno 컬럼을 참조하고, mgr 컬럼은 자기자신 테이블인 EMP 테이블의 empno 컬럼을 참조합니다. 

부서(DEPT) 테이블의 제약조건을 조회 합시다.

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE 

FROM   USER_CONSTRAINTS

WHERE TABLE_NAME = 'DEPT';

<실행결과>

	CONSTRAINT_NAME	CONSTRAINT_TYPE
1	PK_DEPT	P

실습

EMP 테이블에서 외래키를 삭제해보고 다시 만들어 보겠습니다.

사원(EMP) 테이블의 deptno 컬럼에 걸려있는 외래 키를 삭제 후 다시 생성하세요 외래키 가끔은 외래키 제약조건을 비활성화, 활성화, 삭제 하는 경우도 있으므로 생성시 이름을 부여하여 생성하세요.

ALTER TABLE emp DROP CONSTRAINT fk_deptno; ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

실습

실습을 위해 고객과 매출 테이블을 생성하고 데이터를 입력 합시다.

고객(CUSTOMER), 매출(SALES) 테이블을 생성합니다. 1:N 관계로 CUSTOMER가 부모 테이블, SALES가 자식 테이블입니다.

DROP TABLE CUSTOMER;

DROP TABLE SALES;

CREATE TABLE CUSTOMER (

    ID NUMBER PRIMARY KEY,

    NAME VARCHAR2(50) NOT NULL 

);

CREATE TABLE SALES (

    ID NUMBER PRIMARY KEY,

    CUSTOMER_ID NUMBER DEFAULT 9999 REFERENCES CUSTOMER(ID) ,

    SALE_DATE DATE,    

    AMOUNT NUMBER

);

데이터 입력은 부모 테이블부터 입력해야 합니다. 자식 테이블 SALES는 기본 키 컬럼인 id가 부모 테이블의 id 컬럼을 참조하므로 부모 테이블인 CUSTOMER 없이 SALES 데이터가 만들어질 수 없습니다. 

부모테이블부터 데이터를 입력 합시다.

INSERT INTO CUSTOMER VALUES  (1, '1길동');

INSERT INTO SALES VALUES  (1, 1, SYSDATE, 100000);

COMMIT;

<실행결과>

커밋 완료.

실습

참조무결성을 테스트 하기 위해 부모테이블(CUSTOMER)에 없는 id 값으로 자식테이블(SALES)에 입력을 시도해 보겠습니다.

CUSTOMER에 없는 id ‘2’ 로 SALES 테이블에 새행을 입력합시다. 무결성 제약조건 오류가 발생합니다.

INSERT INTO SALES VALUES  (2, 2,SYSDATE, 100000);

<실행결과>

ORA-02291: 무결성 제약조건(SCOTT.SYS_C007527)이 위배되었습니다- 부모 키가 없습니다.

 

#외래키, #ForeignKey, #오라클케이블, #테이블, #오라클강좌, #오라클교육​