🗂

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

2022/08/03に公開

MySQL

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"