▣ 뷰(VIEW)에서 활용되는 WITH CHECK OPTION
WITH CHECK OPTION절은 VIEW에 대한 갱신이나 삽입 연산을 실행할 때 VIEW 정의 조건을 위반하면 실행이 거부된다는 것을 의미하므로
- 투플이 뷰에서 사라지지 않도록 보호하고,
- 데이터 무결성을 유지할 수 있으며,
- INSERT문과 UPDATE문으로 뷰에 정의된 조건에 위배된 투플을 생성할 수 없음.
2012년 51번
정답 : 4번
2014년 51번
정답 : 2번
1번) 기본키 제약조건이 풀렸을 경우, VALUES ('이태훈', '수학') -> VALUES ('이태훈', '컴퓨터') 바꾸면 삽입가능
WITH CHECK OPTION 에러 발생 / VIEW를 만들 때 해당 제약조건 안에서만 삽입 가능
3번) Inner Join형태로 동시에 다중 테이블에 Insert 불가
4번) WITH CHECK OPTION 에러 발생 / VIEW를 만들 때 해당 제약조건 안에서만 삽입 가능
-- 2014년 정보시스템감리사 DB 51번
DROP TABLE ST_51;
DROP TABLE SUB_51;
DROP TABLE PRO_51;
CREATE TABLE ST_51
(
STNO INT
, STNAME VARCHAR(20)
, STMAJ VARCHAR(20)
, PRIMARY KEY (STNO)
)
;
INSERT INTO ST_51 VALUES (1, '김대진', '컴퓨터');
INSERT INTO ST_51 VALUES (2, '이준우', '컴퓨터');
INSERT INTO ST_51 VALUES (3, '황경진', '수학');
INSERT INTO ST_51 VALUES (4, '임성호', '수학');
COMMIT;
SELECT * FROM ST_51;
CREATE TABLE SUB_51
(
JNO INT
, SUB VARCHAR(20)
, PNO INT
, PRIMARY KEY (JNO)
)
;
INSERT INTO SUB_51 VALUES (10, '대수학', 100);
INSERT INTO SUB_51 VALUES (20, '통계학', 200);
INSERT INTO SUB_51 VALUES (30, '전산학', 300);
INSERT INTO SUB_51 VALUES (40, '미적분', 400);
COMMIT;
SELECT * FROM SUB_51;
CREATE TABLE PRO_51
(
PNO INT
, PNAME VARCHAR(20)
, PDEP VARCHAR(20)
, PRIMARY KEY (PNO)
)
;
INSERT INTO PRO_51 VALUES (100, '김병엽', '수학');
INSERT INTO PRO_51 VALUES (200, '박소희', '수학');
INSERT INTO PRO_51 VALUES (300, '이준석', '컴퓨터');
INSERT INTO PRO_51 VALUES (400, '김헤진', '수학');
COMMIT;
SELECT * FROM PRO_51;
-- 1) 보기
DROP VIEW ST_VIEW;
CREATE VIEW ST_VIEW AS SELECT STNAME, STMAJ
FROM ST_51 WHERE STMAJ = '컴퓨터'
WITH CHECK OPTION;
SELECT * FROM ST_VIEW;
INSERT INTO ST_VIEW(STNAME, STMAJ)
VALUES ('이태훈', '수학');
-- 기본키 제약조건이 풀렸을 경우, VALUES ('이태훈', '수학') -> VALUES ('이태훈', '컴퓨터') 바꾸면 삽입가능
-- WITH CHECK OPTION 에러 발생 / VIEW를 만들 때 해당 제약조건 안에서만 삽입 가능
-- 2) 보기
DROP VIEW PRO_VIEW;
CREATE VIEW PRO_VIEW AS SELECT PNO, PNAME
FROM PRO_51;
SELECT * FROM PRO_VIEW;
INSERT INTO PRO_VIEW(PNO, PNAME)
VALUES (500, '김성태');
-- 정답 실행됨 / WITH CHECK OPTION 없음
-- 3) 보기
DROP VIEW SUB_VIEW;
CREATE VIEW SUB_VIEW AS SELECT PNAME, JNO
FROM PRO_51, SUB_51
WHERE PRO_51.PNO = SUB_51.PNO
WITH CHECK OPTION;
SELECT * FROM SUB_VIEW;
INSERT INTO SUB_VIEW(PNAME, JNO)
VALUES ('박정훈', 50);
-- WITH CHECK OPTION 에러 발생 / VIEW를 만들 때 해당 제약조건 안에서만 삽입 가능
-- FROM PRO_51, SUB_51 암시적 조인 표현
-- WHERE PRO_51.PNO = SUB_51.PNO -> Inner Join형태로 동시에 다중 테이블에 Insert 불가
-- 그외에도 기본키가 빠져서 개체 무결성에도 위배됨
-- 4) 보기
DROP VIEW ST_VIEW1;
CREATE VIEW ST_VIEW1 AS SELECT STNO, STNAME
FROM ST_51 WHERE STMAJ = '수학'
WITH CHECK OPTION;
SELECT * FROM ST_VIEW1;
UPDATE ST_VIEW1 SET STMAJ = '컴퓨터'
WHERE STNO = 3;
-- WITH CHECK OPTION 에러 발생 / VIEW를 만들 때 해당 제약조건 안에서만 삽입 가능
-- SK_VIEW1 테이블에 STMAJ 칼럼이 없음
-- 아래와 같이 STNAME으로 하면 실행됨
-- UPDATE ST_VIEW SET STNAME = '황경진'
-- WHERE STNO = 3;
* MySQL workbench에서 table을 UPDATE나 DELETE할 때 where 조건절을 쓰지 않고 할 경우
에러코드 메시지가 나오면서 쿼리를 수행하지 않게됨 -> MySQL Safe mode off
* Materialized VIEW(실체화 뷰)
- 뷰 결과를 물리적인 테이블로 저장
- 질의 처리 성능 향상