[DB] SQL - VIEW의 제약 조건 및 고급 옵션 활용

들어가기 앞서

VIEW에 대해 모르겠다면 아래 블로그에 정리해뒀다.

https://lold2424.tistory.com/215

[DB] SQL - VIEW 개념 파헤치기

제약 조건이 왜 필요할까?

1. 원본 테이블의 데이터 무결성을 유지하기 위해
   • 뷰는 단순히 데이터를 보여주는 역할뿐만 아니라, 데이터를 추가하거나 수정할 수도 있다.
   • 이럴 때, 사용자가 뷰를 통해 잘못된 데이터를 입력하면 원본 테이블의 무결성이 깨질 수 있다.
2. 데이터 보안 및 접근 제어 강화:
   • 뷰는 원본 테이블의 특정 컬럼이나 행만 선택적으로 보여주어 데이터 접근을 제한하는 역할을 한다.
   • 뷰에 제약 조건을 추가하면, 허용된 범위 내에서만 데이터 조작이 가능하도록 통제할 수 있다.
3. 비즈니스 규칙 적용:
   • 뷰는 복잡한 쿼리를 단순화하여 사용자에게 편리한 데이터 접근 방식을 제공한다.
   • 뷰에 제약 조건을 설정하면, 데이터 조작 시 특정 비즈니스 규칙을 따르도록 강제할 수 있다.
4. 데이터 일관성 유지:
   • 여러 사용자가 동일한 뷰를 통해 데이터를 조작하는 경우, 제약 조건은 데이터의 일관성을 유지하는 데 도움을 준다.
   • 모든 데이터 변경 작업이 정의된 규칙을 따르도록 보장하여 데이터의 신뢰성을 높일 수 있다.

즉, 제약 조건은 데이터의 유효성, 무결성을 강제해 데이터를 수정하는 행위에 영향을 준다.

쉽게 설명하자면, 데이터가 DB에 잘못 저장되지 않도록 방지한다는 뜻이다.

제약 조건의 종류와 특징

제약 조건은 아래와 같은 종류로 나뉘며, 그에 따른 역할이 다르다.

제약 조건	설명	특징
PRIMARY KEY	테이블의 고유 식별자	중복 불가 + NULL 불가
UNIQUE	중복되지 않는 값 보장	NULL 허용 (단, 중복되지 않게)
NOT NULL	반드시 값이 있어야 함	NULL 입력 불가
CHECK	지정된 조건만 허용	사용자 정의 조건식 가능
DEFAULT	값이 없을 때 기본값 지정	자동 값 삽입
FOREIGN KEY	다른 테이블의 값 참조	참조 무결성 유지

직접 코드를 통해 한번 알아보자.

1. PRIMARY KEY (기본 키)

CREATE TABLE member (
  member_id INT PRIMARY KEY,
  name VARCHAR(50)
);

2. UNIQUE (고유 제약 조건)

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

3. NOT NULL (널 값 금지)

CREATE TABLE product (
  product_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

4. CHECK (조건 제약)

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT CHECK (age >= 18)
);

5. DEFAULT (기본값)

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending'
);

6. FOREIGN KEY (외래 키)

CREATE TABLE department (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE emp (
  emp_id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

고급 옵션이 왜 필요할까?

위 제약을 통해 데이터의 무결성을 보장했다면 이제 고급 옵션을 통해 뷰의 동작 방식을 제어할 수 있다.

고급 옵션을 통해 데이터 관리 효율성을 증대시키고, 특정 요구 사항을 충족시킬 수 있다.

즉, 고급 옵션은 뷰의 동작 방식, 처리방식, 보안 컨텍스트와 같은 제어하는 과정에서 데이터를 조회하거나 수정하는 과정에 영향을 끼친다.

아래 어떤 옵션들이 있는지 확인해보자.

아래 테이블을 대상으로 예시를 들것이다.

• 사원 정보를 저장하고 있는 emp테이블

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL	20
7698	BLAKE	MANAGER	7839	1981-05-01	2850	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	NULL	10
7839	KING	PRESIDENT	NULL	1981-11-17	5000	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7900	JAMES	CLERK	7698	1981-12-03	950	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300	NULL	10

WITH CHECK OPTION

VIEW를 통해 데이터를 수정할 때, 수정된 값이 VIEW의 조건을 벗어나지 않도록 제한해준다.

VIEW를 사용해서 데이터를 수정하려면 INSERT, UPDATE를 사용해야하는데 이때 무결성을 보장해준다.

• 사용 예시

CREATE VIEW v_emp_it AS
SELECT * FROM emp WHERE deptno = 20
WITH CHECK OPTION;

-- 아래는 실패! (deptno가 10이므로 뷰 조건에 맞지 않음)
UPDATE v_emp_it SET deptno = 10 WHERE empno = 7902;

SQL SECURITY

VIEW 실행 시 어느 사용자의 권한으로 실행할지를 설정한다.

• DEFINER: 뷰를 만든 사람의 권한으로 실행됨 (기본값)
• INVOKER: 뷰를 실행한 사용자의 권한으로 실행됨
• 사용 예시

CREATE VIEW v_emp_public
SQL SECURITY INVOKER
AS
SELECT ename, sal FROM emp;

ALGORITHM

VIEW가 어떻게 실행될지를 결정하는 방식이다.

• MERGE: 뷰를 원본 테이블 쿼리로 병합 (빠름)
• TEMPTABLE: 뷰 결과를 임시 테이블에 담아 처리 (메모리 사용 ↑, 일부 기능 가능)
• UNDEFINED: MySQL이 자동 선택
• 사용 예시

CREATE ALGORITHM = MERGE VIEW v_emp_hr AS
SELECT * FROM emp WHERE deptno = 10;