目次
はじめに
既存テーブルのカラムのデータ型を変更しようとした際に、エラーが発生することがあります。今回の記事では、そのエラーが該当カラムに制約が付いていることが原因で発生した場合について、解決方法を紹介します。
PRIMARY KEY制約に起因した場合
エラー内容
PRIMARY KEY制約の付いたカラムに対して、データ型のサイズを変更しようとすると、以下のようなエラーが発生します。|
1 2 |
ALTER TABLE T_PK ALTER COLUMN COL2 SET DATA TYPE VARCHAR(20); ROLLBACK 2350: Cannot alter type of column "COL2" since it is referenced in the constraint "C_PRIMARY" |
対処
該当のカラムからPRIMARY KEY制約を一旦外し、データ型のサイズを変更します。その後に再度PRIMARY KEY制約を設定することで解決することができます。
以下にSQLコマンドを交えて対処方法を紹介します。
制約名を確認するには、以下のSQLを使用します。
|
1 2 3 4 |
SELECT * FROM TABLE_CONSTRAINTS; constraint_id | constraint_name | constraint_schema_id | constraint_key_count | foreign_key_count | table_id | table_name | foreign_table_id | constraint_type | is_enabled | predicate -------------------+-----------------+----------------------+----------------------+-------------------+-------------------+------------+------------------+-----------------+------------+------------------ 45035996273711236 | C_PRIMARY | 45035996273704980 | 1 | 0 | 45035996273711040 | t_pk | 0 | p | f | |
|
1 2 3 4 5 6 7 8 9 10 11 |
テーブルから制約を外します ALTER TABLE T_PK DROP CONSTRAINT C_PRIMARY; ALTER TABLE カラムのデータ型を拡張します ALTER TABLE T_PK ALTER COLUMN COL2 SET DATA TYPE VARCHAR(20); ALTER TABLE テーブルに制約を追加します。 ALTER TABLE T_PK ADD CONSTRAINT PK PRIMARY KEY(COL2); ALTER TABLE |
FOREIGN KEY制約に起因した場合
エラー内容
FOREIGN KEY制約の付いたカラムに対して、データ型のサイズを変更しようとすると、以下のようなエラーが発生します。|
1 2 |
ALTER TABLE T_FK ALTER COLUMN COL_FK SET DATA TYPE VARCHAR(20); ROLLBACK 2350: Cannot alter type of column "COL_FK" since it is referenced in the constraint "C_FOREIGN" |
対処
該当のカラムからFOREIGN KEY制約を一旦外し、データ型のサイズを変更します。その後に再度FOREIGN KEY制約を設定することで解決することができます。
以下にSQLコマンドを交えて対処方法を紹介します。
制約名を確認するには、以下のSQLを使用します。
|
1 2 3 4 |
SELECT * FROM TABLE_CONSTRAINTS; constraint_id | constraint_name | constraint_schema_id | constraint_key_count | foreign_key_count | table_id | table_name | foreign_table_id | constraint_type | is_enabled | predicate -------------------+-----------------+----------------------+----------------------+-------------------+-------------------+------------+-------------------+-----------------+------------+------------------ 45035996273711660 | C_FOREIGN | 45035996273704980 | 1 | 1 | 45035996273711658 | T_FK | 45035996273711654 | f | | |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
テーブルから制約を外します ALTER TABLE T_FK DROP CONSTRAINT C_FOREIGN RESTRICT; ALTER TABLE カラムのデータ型を拡張します ALTER TABLE T_FK ALTER COLUMN COL_FK SET DATA TYPE VARCHAR(20); ALTER TABLE つぎに、先の手順で外したFOREIGN KEY制約を追加しますが、FOREIGN KEYの参照先となるテーブルのカラムのデータ型をと一致しない場合は、以下のようなエラーが発生します。 ALTER TABLE T_FK ADD CONSTRAINT C_FOREIGN FOREIGN KEY(COL_FK) REFERENCES T1(COL2); ROLLBACK 3531: Incompatible data types between primary and foreign key columns: fk: col_fk, pk: col2 このような場合は、参照先のデータ型を変更しておきます。 参照先のデータ型を変更したら、該当のカラムに制約を追加します。 ALTER TABLE t_pk ADD CONSTRAINT C_FOREIGN FOREIGN KEY(col_fk) references t1(col2); ALTER TABLE |
UNIQUE制約に起因した場合
エラー内容
UNIQUE制約の付いたカラムに対して、データ型のサイズを変更しようとすると、以下のようなエラーが発生します。|
1 2 |
ALTER TABLE T_UNQ ALTER COLUMN COL2 SET DATA TYPE VARCHAR(20); ROLLBACK 2350: Cannot alter type of column "COL2" since it is referenced in the constraint "C_UNIQUE" |
対処
該当のカラムからUNIQUE制約を一旦外し、データ型のサイズを変更します。その後に再度UNIQUE制約を設定することで解決することができます。
以下にSQLコマンドを交えて対処方法を紹介します。
制約名を確認するには、以下のSQLを使用します。
|
1 2 3 4 |
SELECT * FROM TABLE_CONSTRAINTS; constraint_id | constraint_name | constraint_schema_id | constraint_key_count | foreign_key_count | table_id | table_name | foreign_table_id | constraint_type | is_enabled | predicate -------------------+-----------------+----------------------+----------------------+-------------------+-------------------+------------+-------------------+-----------------+------------+------------------ 45035996273710548 | C_UNIQUE | 45035996273704980 | 1 | 0 | 45035996273710430 | t_unq | 0 | u | f | |
|
1 2 3 4 5 6 7 8 9 10 11 |
テーブルから制約を外します ALTER TABLE T_UNQ DROP CONSTRAINT C_UNIQUE ; ALTER TABLE カラムのデータ型を拡張します ALTER TABLE T_UNQ ALTER COLUMN COL2 SET DATA TYPE VARCHAR(20); ALTER TABLE テーブルに制約を追加します。 ALTER TABLE T_UNQ ADD CONSTRAINT C_UNIQUE UNIQUE(COL2) ; ALTER TABLE |
CHECK制約に起因した場合
エラー内容
UNIQUE制約の付いたカラムに対して、データ型のサイズを変更しようとすると、以下のようなエラーが発生します。|
1 2 |
ALTER TABLE T_CHK ALTER COLUMN COL2 SET DATA TYPE VARCHAR(20); ROLLBACK 2350: Cannot alter type of column "col2" since it is referenced in the constraint "C_CHECK" |
対処
該当のカラムからCHECK制約を一旦外し、データ型のサイズを変更します。その後に再度CEHCK制約を設定することで解決することができます。
以下にSQLコマンドを交えて対処方法を紹介します。
制約名を確認するには、以下のSQLを使用します。
|
1 2 3 4 |
SELECT * FROM TABLE_CONSTRAINTS; constraint_id | constraint_name | constraint_schema_id | constraint_key_count | foreign_key_count | table_id | table_name | foreign_table_id | constraint_type | is_enabled | predicate -------------------+-----------------+----------------------+----------------------+-------------------+-------------------+------------+-------------------+-----------------+------------+------------------ 45035996273711302 | C_CHECK | 45035996273704980 | 1 | 0 | 45035996273711298 | t_chk | 0 | c | t | (t_chk.col2 > 1) |
|
1 2 3 4 5 6 7 8 9 10 11 |
テーブルから制約を外します alter table t_chk drop constraint C_CHECK; ALTER TABLE カラムのデータ型を拡張します alter table t_chk alter column col2 set data type varchar(20); ALTER TABLE テーブルに制約を追加します。 ALTER TABLE t_chk ADD CONSTRAINT C_CHECK CHECK (col2>1); ALTER TABLE |
DEFAULT制約に起因した場合
エラー内容
DEFAULT制約の付いたカラムに対して、データ型のサイズを変更しようとすると、以下のようなエラーが発生します。Vertica 9.0以降では当エラーは発生することなくデータ型のサイズを変更することが可能です。
|
1 2 3 |
dbadmin=> ALTER TABLE T1 ALTER COLUMN COL3 SET DATA TYPE VARCHAR(20); ROLLBACK 2354: Cannot alter type of column with a default expression HINT: Drop column default before altering datatype |
対処
該当のカラムからDEFAULT制約を一旦外し、データ型を変更します。その後に再度DEFAULT制約を設定することで解決することができます。
以下にSQLコマンドを交えて対処方法を紹介します。
|
1 2 3 4 5 6 7 8 9 10 11 |
テーブルから制約を外します ALTER TABLE T1 ALTER COLUMN COL3 DROP DEFAULT ; ALTER TABLE カラムのデータ型を拡張します ALTER TABLE T1 ALTER COLUMN COL3 SET DATA TYPE VARCHAR(20) ; ALTER TABLE テーブルに制約を追加します。 ALTER TABLE T1 ALTER COLUMN COL3 SET DEFAULT '@@@' ; ALTER TABLE |
セグメンテーションキーに起因した場合
エラー内容
プロジェクションのセグメンテーションキーとして参照されているカラムに対して、データ型のサイズを変更しようとすると、以下のようなエラーが発生します。|
1 2 |
ALTER TABLE T1 ALTER COLUMN COL3 SET DATA TYPE VARCHAR(20); ROLLBACK 2353: Cannot alter type of column "COL3" since it is referenced in the segmentation expression of projection "t1_DBD_1_seg_comp01_b1" |
プロジェクションのセグメンテーションキーとは・・・
プロジェクションを作成する際に使われるもので、データ分散を行う際にキーとなるカラムが指定されます。
Verticaシステムを複数のノードで構成した場合に、データは複数のノードで分散して持たせます。
データの分散方法として、セグメンテーションとレプリケーションの2つの方法があります。
セグメンテーションとレプリケーションの違いについては、以下の記事をご参照ください。
セグメンテーションとレプリケーションの概要
http://vertica-tech.ashisuto.co.jp/segmentation_replication_overview/
今回エラーはセグメンテーションに関連した問題です。プロジェクションを作成する際に使われるもので、データ分散を行う際にキーとなるカラムが指定されます。
Verticaシステムを複数のノードで構成した場合に、データは複数のノードで分散して持たせます。
データの分散方法として、セグメンテーションとレプリケーションの2つの方法があります。
セグメンテーションとレプリケーションの違いについては、以下の記事をご参照ください。
セグメンテーションとレプリケーションの概要
http://vertica-tech.ashisuto.co.jp/segmentation_replication_overview/
プロジェクションを作成する際、以下のようなCREATE PROJECTION文が発行されます。
その中に、SEGMENTED BY句で指定されたカラムがあります。(★印の箇所)
これは、指定したカラムが持つデータをハッシュ関数を使ってデータを分散することを意味します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE PROJECTION t1_DBD_1_seg_comp01_b0 /*+basename(t1_DBD_1_seg_comp01),createtype(D)*/ ( col1 ENCODING AUTO, col2 ENCODING AUTO, col3 ENCODING AUTO ) AS SELECT col1, col2, col3 FROM public.t1 ORDER BY col3 SEGMENTED BY MODULARHASH (col1, col2, col3) ALL NODES OFFSET 0;★ CREATE PROJECTION t1_DBD_1_seg_comp01_b1 /*+basename(t1_DBD_1_seg_comp01),createtype(D)*/ ( col1 ENCODING AUTO, col2 ENCODING AUTO, col3 ENCODING AUTO ) AS SELECT col1, col2, col3 FROM public.t1 ORDER BY col3 SEGMENTED BY MODULARHASH (col1, col2, col3) ALL NODES OFFSET 1;★ |
従って、データ型を変更しようとするカラムがSEGMENTED BY句で指定されていると、
そのSQLはエラーになってしまいます。
このような場合は、以下のいずれかの方法で対処が可能です。
対処
①テーブルを再作成する
②カラムのデータ型変更後のテーブルを別途作成し、既存テーブルと置き換える方法
①はシンプルな方法ですが、テーブルが存在しなくなる期間が生じてしまうため、サービスへの影響が出る可能性があります。②カラムのデータ型変更後のテーブルを別途作成し、既存テーブルと置き換える方法
②は少々複雑な手順が必要になりますが、サービスへの影響を最小限に留められるメリットがあります。
対処方法として、②の方法について例を紹介します。
|
1 2 3 4 5 6 7 8 9 10 11 |
(例) 既存のテーブルをt1とし、t1テーブルの一部のカラムのデータ型を変更したテーブルをt2とします。 1. データ型を変更したt2テーブルを作成します。 2. t2のスーパープロジェクションを手動で作成します。 この時、t1のスーパープロジェクションを参考にしながら、スーパープロジェクションの名前が重複しないように注意します。 3. t1のデータをt2にコピーします。 INSERT /*+ DIRECT */ INTO t2 SELECT * FROM t1; 4. t2テーブルをt1テーブルにリネームします。 ALTER TABLE t1, t2, temps RENAME TO temps, t1, t2 ; 5. t2(旧t1)テーブルを削除します。 DROP TABLE文にCASCADEをオプションを付与して、t2(旧t1)に関連するプロジェクションを削除します。 DROP TABLE t2 CASCADE; |
テーブルのリネームについては、以下の記事をご参照ください。
テーブルのRENAMEコマンド
http://vertica-tech.ashisuto.co.jp/table_rename/
検証バージョンについて
この記事の内容はVertica 23.4で確認しています。更新履歴
2023/12/21 Vertica23.4用に検証バージョンを修正2018/05/18 本記事を公開
