키 보존 테이블 (Key-Preserved Table) in Oracle

by

1. 키 보존 테이블이란?

키 보존 테이블(Key-Preserved Table) 은 View(뷰)에서 특정 테이블의 기본 키(Primary Key)가 유지되는 테이블을 의미합니다. (뷰에 rowid를 제공하는 테이블)
즉, 뷰를 통해 SELECT, INSERT, UPDATE, DELETE 작업을 수행할 때, 기본 키(Primary Key)가 그대로 유지되는 테이블을 말합니다.

📌 주요 개념:

  • 뷰(View)에서 기본 키(Primary Key)가 유지되는 테이블
  • 해당 테이블의 기본 키가 뷰에서 유일성을 유지하는 경우
  • 업데이트(UPDATE), 삭제(DELETE), 삽입(INSERT)가 가능한 테이블
  • JOIN된 뷰에서도 특정 테이블이 키 보존되면 해당 테이블에 대해 DML 작업이 가능

2. 키 보존 테이블의 필요성

Oracle에서 뷰(View)를 사용하면 데이터를 읽기 전용(READ-ONLY) 으로 볼 수 있습니다.
그러나 일부 뷰에서는 INSERT, UPDATE, DELETE 같은 DML(데이터 조작) 작업이 허용됩니다.
이때, 키 보존 테이블이 포함된 뷰에서만 해당 테이블의 데이터 수정이 가능합니다.

✅ 즉, 키 보존 테이블이 아니면 뷰를 통해 DML을 실행할 수 없음!

3. 키 보존 테이블의 예제

✅ 단일 테이블의 경우 (키 보존 테이블)

뷰가 단일 테이블을 기반으로 할 때, 해당 테이블의 기본 키가 유지되므로 키 보존 테이블이 됩니다.

CREATE VIEW emp_view AS 
SELECT employee_id, first_name, last_name, department_id 
FROM employees;
  • employees 테이블의 기본 키(PK: employee_id)가 뷰에서도 유지됨.
  • 따라서 emp_view에서 UPDATE, INSERT, DELETE 수행 가능
집UPDATE emp_view 
SET last_name = 'Smith' 
WHERE employee_id = 100;  -- ✅ 성공 (키 보존 테이블이므로 가능)

✅ JOIN이 포함된 경우 (키 보존 테이블 여부 확인)

👉 JOIN된 뷰에서는 기본 키가 유지되는 테이블만 DML 가능

CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
  • employees의 기본 키 employee_id는 뷰에서도 유일성을 유지
  • 그러나 departments의 department_id는 중복될 가능성이 있음
  • 따라서 employees는 키 보존 테이블이지만, departments는 키 보존 테이블이 아님.
UPDATE emp_dept_view 
SET first_name = 'John' 
WHERE employee_id = 101;  -- ✅ 성공 (키 보존 테이블인 employees에 대한 변경)

UPDATE emp_dept_view 
SET department_name = 'HR' 
WHERE department_id = 10;  -- ❌ 오류 (departments 테이블은 키 보존되지 않음)

4. 키 보존 테이블이 되기 위한 조건

  1. 뷰 내에서 테이블의 기본 키(Primary Key)가 그대로 유지되어야 함.
  2. 기본 키 값이 중복되지 않아야 함.
  3. JOIN된 뷰라면, 특정 테이블의 행이 고유하게 식별 가능해야 함.
  4. 뷰에서 DISTINCT, GROUP BY, AGGREGATE FUNCTIONS(SUM, COUNT, AVG 등)을 사용하지 않아야 함.
    • 이러한 연산이 들어가면 기본 키를 보존할 수 없기 때문에 DML 불가능.

5. 키 보존되지 않는 테이블 예시

1) DISTINCT 사용 시 키 보존 X

CREATE VIEW emp_distinct AS 
SELECT DISTINCT department_id FROM employees;
  • DISTINCT를 사용하면 기본 키(employee_id)가 사라짐.
  • 따라서 emp_distinct 뷰에서는 UPDATE 불가능.
UPDATE emp_distinct 
SET department_id = 50 WHERE department_id = 10;  
-- ❌ 오류: 기본 키가 보존되지 않으므로 UPDATE 불가능

2) GROUP BY 사용 시 키 보존 X

CREATE VIEW emp_grouped AS
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;
  • GROUP BY를 사용하면 기본 키(employee_id)가 유지되지 않음.
  • 따라서 emp_grouped 뷰에서는 UPDATE, INSERT, DELETE 불가능.
UPDATE emp_grouped 
SET emp_count = 10 WHERE department_id = 20;
-- ❌ 오류: 키 보존되지 않음 (기본 키 X)

6. INSTEAD OF TRIGGER를 사용한 DML 수행

키 보존되지 않는 테이블이 있는 뷰에서도 INSTEAD OF TRIGGER를 사용하면 DML 가능.

CREATE OR REPLACE TRIGGER emp_dept_trigger
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
    UPDATE employees 
    SET first_name = :NEW.first_name
    WHERE employee_id = :OLD.employee_id;
END;
  • 위 트리거를 사용하면 emp_dept_view에서 employees 테이블에 대한 UPDATE 가능.

7. 정리

경우키 보존 여부DML 가능 여부
단일 테이블 뷰 (기본 키 유지)✅ 가능✅ 가능
JOIN 뷰에서 기본 키 유지✅ 가능✅ 가능 (해당 테이블만)
JOIN 뷰에서 기본 키 유지되지 않음❌ 불가능❌ 오류 발생
DISTINCT, GROUP BY 포함❌ 불가능❌ 오류 발생
INSTEAD OF TRIGGER 사용✅ 가능✅ 가능

💡 결론:

  • 키 보존 테이블이란 뷰에서 기본 키가 유지되는 테이블.
  • 키 보존되는 테이블만 DML(INSERT, UPDATE, DELETE) 가능.
  • DISTINCT, GROUP BY, 집계 함수가 포함된 경우 키 보존 X → DML 불가능.
  • INSTEAD OF TRIGGER를 사용하면 키 보존되지 않는 테이블도 DML 가능.

🚀 뷰에서 데이터를 수정하고 싶다면, 키 보존 여부를 꼭 확인해야 합니다!

Leave a Comment