🗂

ビューで使われているカラムの定義を変えたときの挙動まとめ

2022/08/02に公開約5,800字

SQL Server を使っていて、ビューが参照しているカラムの長さを変更してもビュー側の長さが変わらないという現象にあった。
他の DB はどうなんだろう?と思ったので検証してみました。

環境

今回は Docker 上に環境を構築します。

名称 バージョン
MySQL 8.0.30
PostgreSQL 14.0
SQL Server 2019 15.0.4236.7
Docker の設定

ディレクトリ構成

database/init 配下に初期化用のクエリをまとめています。

database/infra 配下に Dockerfile を配置しています。

.
├─database
│  └─init
│      ├─mysql
│      ├─postgresql
│      └─sqlserver
└─infra
   ├─mysql
   ├─postgresql
   └─sqlserver
docker-compse.yml
version: '3.9'

services:
  mysql:
    build: ./infra/mysql
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
    volumes:
      - ./database/init/mysql:/docker-entrypoint-initdb.d
    ports:
      - 3306:3306

  postgresql:
    build: ./infra/postgresql
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'postgres'
    volumes:
      - ./database/init/postgresql:/docker-entrypoint-initdb.d
    ports:
      - 5432:5432

  sqlserver:
    build: ./infra/sqlserver
    environment:
      ACCEPT_EULA: 'Y'
      SA_PASSWORD: 'rootRootr00t'
    volumes:
      - ./database/init/sqlserver:/docker-entrypoint-initdb.d
    ports:
      - 1433:1433

検証方法

それぞれの DB で準備しておいたクエリを流します。(下記クエリは MySQL 用です。)

-- DBの作成
CREATE DATABASE sample;

USE sample;

-- テーブルの作成
CREATE TABLE users (
  id INT,
  name VARCHAR(10),
  PRIMARY KEY (id)
);

-- VIEWの作成
CREATE VIEW user_view AS
SELECT
  *
FROM
  users;

-- データ投入
INSERT INTO
  users(id, name)
VALUES
  (1, 'hoge太郎'),
  (2, 'fuga次郎'),
  (3, 'piyo三郎');

-- 準備ができた状態で、テーブルとビューの定義を見る
DESC users;
DESC user_view;

-- カラムの定義を変更
-- 今回はnameカラムを VARCHAR(10) から VARCHAR(20) にする。
ALTER TABLE users MODIFY name VARCHAR(20);

-- 変更後の定義を見る
DESC users;
DESC user_view;

結果

DB 挙動
MySQL ALTER成功。自動的にビュー側の定義も変わる。
PostgreSQL ALTER失敗。ビューなどで参照しているカラムを変更することはできない。
SQL Server ALTER成功。ビュー側の定義は変わらない。
クエリの実行結果

MySQL

ALTER 文実行後、ビューの定義も連動して変わっています。

変更前

-- テーブル
mysql> DESC users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- ビュー
mysql> DESC user_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

変更後

-- カラム定義変更
mysql> ALTER TABLE users MODIFY name VARCHAR(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- テーブル
mysql> desc users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- ビュー
mysql> DESC user_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

PostgreSQL

ALTER 文がエラーにより実行できませんでした。
ビューで参照している場合は変更できないようです。

変更前

-- テーブル
sample=# \d users;
                      Table "public.users"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null |
 name   | character varying(10) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

-- ビュー
sample=# \d user_view;
                     View "public.user_view"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 name   | character varying(10) |           |          |

変更後

-- カラム定義変更
sample=# ALTER TABLE users ALTER name TYPE VARCHAR(20);
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view user_view depends on column "name"

SQL Server

ALTER 文が成功してもビューの定義は変わりませんでした。

変更前

変更後

定義は古いですが、データの取得は正常にできました。

まとめ

PostgreSQL が定義変更できないようになっているのには驚きました。
一見すると面倒な仕様に思えますが、アプリケーションを安全に保つことができるという面では一番いい挙動だと感じています。

SQL Server もビュー生成時にオプションをつけることで同様の挙動をするようにできるようです。
個人的にはデフォルトでそうして欲しかったです。

MySQL は連動して変わってくれるので型の変更に関しては何も考えなくてよさそうですね。
ただし、カラムの追加や削除は連動しないようなので、追加削除を行った場合は適宜再構築が必要です。(これは SQL Server も同様です。)

参考

MySQL :: MySQL 8.0 Reference Manual :: 13.1.23 CREATE VIEW Statement
PostgreSQL: Documentation: 14: CREATE VIEW
CREATE VIEW (Transact-SQL) - SQL Server | Microsoft Docs

GitHubで編集を提案

Discussion

ログインするとコメントできます