27.6.3 Updatable and Insertable Views

27.6.3 Updatable and Insertable Views

일부 view는 updatable하며, 이에 대한 참조는 데이터 변경 문장에서 갱신할 테이블을 지정하는 데 사용할 수 있습니다. 즉, 이러한 view를 UPDATE, DELETE, INSERT 같은 문장에서 사용하여 기본 테이블의 내용을 갱신할 수 있습니다.

Derived table과 common table expression도 multiple-table UPDATE 및 DELETE 문장에서 지정할 수 있지만, 갱신 또는 삭제할 row를 지정하기 위한 데이터 읽기 용도로만 사용할 수 있습니다.
일반적으로 view 참조는 updatable해야 하며, 이는 view가 머터리얼라이즈되지 않고 머지될 수 있어야 함을 의미합니다. Composite view는 더 복잡한 규칙을 가집니다.

view가 updatable이 되려면, view의 row와 기본 테이블의 row 사이에 1:1 관계가 있어야 합니다.
또한 view를 nonupdatable로 만드는 몇 가지 다른 구성 요소들이 있습니다.
보다 구체적으로 말하면, view가 다음 중 어느 하나라도 포함하면 updatable이 아닙니다:

• Aggregate 함수 또는 윈도 함수 ( SUM(), MIN(), MAX(), COUNT() 등)

• DISTINCT

• GROUP BY

• HAVING

• UNION 또는 UNION ALL

• select 리스트에 있는 subquery

select 리스트에 있는 nondependent subquery는 INSERT에서는 실패하지만, UPDATE, DELETE에서는 허용됩니다.
select 리스트에 있는 dependent subquery의 경우에는 어떤 데이터 변경 문장도 허용되지 않습니다.

• 특정 join (이 섹션 뒤에서 추가 join 설명 참조)

• FROM 절에서 nonupdatable view에 대한 참조

• FROM 절에 있는 테이블을 참조하는 WHERE 절의 subquery

• literal 값만 참조하는 경우 (이 경우 갱신할 기본 테이블이 없음)

• ALGORITHM = TEMPTABLE (temporary 테이블을 사용하면 view는 항상 nonupdatable이 됨)

• 기본 테이블의 어떤 column에 대해서든 복수 번의 참조 (INSERT에서는 실패, UPDATE, DELETE에서는 허용)

view의 generated column은 값 할당이 가능하기 때문에 updatable로 간주됩니다.
그러나 이러한 column을 명시적으로 갱신하는 경우 허용되는 유일한 값은 DEFAULT입니다.
generated column에 대한 자세한 내용은 Section 15.1.24.8, “CREATE TABLE and Generated Columns”을 참조하십시오.

multiple-table view가 updatable이 되는 것도 가능한데, 이는 view가 MERGE 알고리즘으로 처리될 수 있다고 가정하는 경우입니다.
이를 위해 view는 inner join을 사용해야 하며(outer join이나 UNION은 사용하면 안 됨), view 정의에서 단 하나의 테이블만 갱신할 수 있어야 하므로 SET 절에는 view에 포함된 테이블 중 하나의 column만 지정되어야 합니다.
UNION ALL을 사용하는 view는 이론적으로 updatable일 수 있다 하더라도 허용되지 않습니다.

insertability 측면에서(즉, INSERT 문장으로 updatable인지 여부), updatable view는 다음과 같은 view column에 대한 추가 조건을 모두 만족하면 insertable입니다:

• 중복된 view column 이름이 없어야 합니다.

• view에는 default 값을 가지지 않는 기본 테이블의 모든 column이 포함되어야 합니다.

• view column은 단순 column 참조여야 합니다. 다음과 같은 expression이면 안 됩니다:

13.14159
2col1 + 3
3UPPER(col2)
4col3 / col4
5(subquery)

MySQL은 CREATE VIEW 시점에 view updatability flag라는 flag를 설정합니다.
이 flag는 UPDATE 및 DELETE(및 이와 유사한 작업)이 view에 대해 합법일 경우 YES(true)로 설정됩니다. 그렇지 않으면 flag는 NO(false)로 설정됩니다.
Information Schema의 VIEWS 테이블에 있는 IS_UPDATABLE column이 이 flag의 상태를 표시합니다.
이는 서버가 view가 updatable인지 여부를 항상 알고 있음을 의미합니다.

view가 updatable이 아니면, UPDATE, DELETE, INSERT 같은 문장은 합법이 아니며 거부됩니다.
(view가 updatable이라 하더라도, 이 섹션의 다른 부분에 설명된 것처럼 view에 INSERT를 할 수 없을 수도 있습니다.)

view의 updatability는 updatable_views_with_limit 시스템 변수의 값에 의해 영향을 받을 수 있습니다.
Section 7.1.8, “Server System Variables”을 참조하십시오.

다음 논의를 위해, 다음과 같은 테이블과 view가 존재한다고 가정합니다:

1CREATE TABLE t1 (x INTEGER);
2CREATE TABLE t2 (c INTEGER);
3CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
4CREATE VIEW vup AS SELECT * FROM t2;
5CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;

INSERT, UPDATE, DELETE 문장은 다음과 같이 허용됩니다:

• INSERT:
  INSERT 문장의 insert 테이블은 머지되는 view 참조일 수 있습니다.
  view가 join view인 경우, view의 모든 구성 요소가 updatable(즉, 머터리얼라이즈되지 않음)이어야 합니다.
  multiple-table updatable view의 경우, INSERT는 단일 테이블에 insert하는 경우에 동작할 수 있습니다.

이 문장은 join view의 구성 요소 중 하나가 nonupdatable이기 때문에 잘못되었습니다:

1INSERT INTO vjoin (c) VALUES (1);

이 문장은 올바릅니다. view에 머터리얼라이즈된 구성 요소가 없기 때문입니다:

1INSERT INTO vup (c) VALUES (1);

• UPDATE:
  UPDATE 문장에서 갱신할 테이블은 머지되는 view 참조일 수 있습니다.
  view가 join view인 경우, view의 구성 요소 중 최소 하나는 updatable이어야 합니다 (이는 INSERT와 다릅니다).

multiple-table UPDATE 문장에서, 문장의 updated 테이블 참조는 기본 테이블이거나 updatable view 참조여야 합니다.
갱신되지 않는 테이블 참조는 머터리얼라이즈드 view나 derived table일 수 있습니다.

이 문장은 올바릅니다. column c는 join view의 updatable 부분에 속하기 때문입니다:

1UPDATE vjoin SET c=c+1;

이 문장은 잘못되었습니다. column x는 nonupdatable 부분에 속하기 때문입니다:

1UPDATE vjoin SET x=x+1;

이 문장은 올바릅니다. multiple-table UPDATE의 updated 테이블 참조가 updatable view(vup)이기 때문입니다:

1UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
2SET c=c+1;

이 문장은 잘못되었습니다. 머터리얼라이즈드 derived table을 갱신하려고 시도하기 때문입니다:

1UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
2SET s=s+1;

• DELETE:
  DELETE 문장에서 삭제 대상이 되는 테이블은 머지된 view여야 합니다.
  join view는 허용되지 않습니다 (이는 INSERT 및 UPDATE와 다릅니다).

이 문장은 view가 join view이기 때문에 잘못되었습니다:

1DELETE vjoin WHERE ...;

이 문장은 view가 머지된(updatable) view이기 때문에 올바릅니다:

1DELETE vup WHERE ...;

이 문장은 머지된(updatable) view에서 삭제하기 때문에 올바릅니다:

1DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;

추가 논의와 예제가 이어집니다.

이 섹션의 앞부분에서는 view에 expression 또는 composite expression인 column이 포함되어 있는 경우, 모든 column이 단순 column 참조가 아니므로 view는 insertable이 아니라고 언급했습니다.
이러한 view는 insertable이 아니지만, expression이 아닌 column만 갱신하는 경우 updatable일 수 있습니다.
다음 view를 고려해 보십시오:

1CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

이 view는 col2가 expression이므로 insertable이 아닙니다.
그러나 col2를 갱신하려 하지 않는 한 updatable입니다.
다음 update는 허용됩니다:

1UPDATE v SET col1 = 0;

다음 update는 expression column을 갱신하려 하기 때문에 허용되지 않습니다:

1UPDATE v SET col2 = 0;

테이블에 AUTO_INCREMENT column이 포함된 경우, 해당 column을 포함하지 않는 테이블에 대한 insertable view에 insert를 수행하더라도 LAST_INSERT_ID()의 값은 변경되지 않습니다.
이는 view의 일부가 아닌 column에 default 값을 insert하는 부수 효과가 보여서는 안 되기 때문입니다.