ALTER TABLE (SQL)

特権とロック

ALTER TABLE コマンドは特権を必要とする操作です。ALTER TABLE を実行するには、ユーザは %ALTER_TABLE 管理特権を持っている必要があります。持っていない場合、SQLCODE –99 エラーが発生し、%msg が "ユーザ 'name' は %ALTER_TABLE 特権を持っていません" に設定されます。

ユーザは、指定されたテーブルに対する %ALTER 特権を持っている必要があります。ユーザがテーブルの所有者 (作成者) である場合、ユーザにはそのテーブルに対する %ALTER 特権が自動的に付与されます。そうでない場合は、ユーザにテーブルに対する %ALTER 特権を付与する必要があります。持っていない場合、SQLCODE –99 エラーが発生し、%msg が "ユーザ 'name' は 'Schema.TableName' のテーブル定義を変更するために必要な %ALTER 特権を持っていません。" に設定されます。

現在のユーザが %ALTER 特権を持っているかどうかを確認するには、%CHECKPRIV コマンドを呼び出します。指定のユーザが %ALTER 特権を持っているかどうかを確認するには、$SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab メソッドを呼び出します。

必要な管理特権を割り当てるには、%ALTER_TABLE 特権で GRANT コマンドを使用します。これには適切な付与特権が必要です。%ALTER オブジェクト特権を割り当てるには、以下を使用できます。

• GRANT コマンドと %ALTER 特権。これには適切な付与特権が必要です。

• ロールまたはユーザを編集するためページの管理ポータルの [SQL Tables] タブにある、目的のテーブルの [ALTER] チェック・ボックス。これには適切な付与特権が必要です。

埋め込み SQL では、以下のように $SYSTEM.Security.Login()Opens in a new tab メソッドを使用して適切な特権を持ったユーザとしてログインできます。

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

$SYSTEM.Security.Login メソッドを呼び出すには、%Service_Login:Use 特権が必要です。詳細は、"インターシステムズ・クラス・リファレンス" の "%SYSTEM.SecurityOpens in a new tab" を参照してください。

• ALTER TABLE は、テーブル・クラスの定義に [DdlAllowed] が含まれている場合を除き、永続クラスから投影されたテーブルでは使用できません。使用すると、操作は SQLCODE -300 エラーで失敗し、%msg が “DDL がクラス schema.tablename に対して有効になっていません” に設定されます。

• ALTER TABLE は、導入済みの永続クラスから投影されたテーブルでは使用できません。この操作は SQLCODE -400 エラーで失敗し、%msg が “導入済みのクラス classname を変更する DDL を実行できません” に設定されます。

ALTER TABLE は、table に対してテーブルレベルのロックを取得します。これにより、他のプロセスはこのテーブルのデータを変更できなくなります。このロックは ALTER TABLE 操作が終了すると自動的に解除されます。ALTER TABLE は対応するクラス定義をロックする際に、現在のプロセスに対して SQL ロック・タイムアウト設定を使用します。

テーブルを変更する場合、そのテーブルを別のプロセスによって EXCLUSIVE MODE または SHARE MODE でロックしないでください。ロックされているテーブルを変更しようとすると、SQLCODE -110 エラーになり、%msg は "テーブル 'Sample.MyTest' の排他テーブルロックを取得できません" になります。

RENAME Table

以下の構文を使用して、既存のテーブルの名前を変更できます。

ALTER TABLE schema.TableName RENAME NewTableName

ADD COLUMN の制限

ADD COLUMN では、単一の列、またはコンマ区切りの列のリストを追加できます。

ALTER TABLEtablename ADD COLUMN 文を使用してテーブルにフィールドを追加することを考えます。

• その名前の列が既に存在している場合、この文は失敗し SQLCODE -306 エラーが発生します。

• この文で、列に NOT NULL 制約を設定し、さらに既定値を設定しない場合、そのテーブルに既にデータがあると、この文はエラーになります。これは、DDL 文が完了した後では NOT NULL 制約は既存の行すべてを満たさないためです。その結果、エラー・コード SQLCODE -304 が生成されますが、これはデータが存在するテーブルに、既定値のない NOT NULL フィールドを追加しようとするためです。

• この文で、列に NOT NULL 制約を設定し、さらに既定値を設定した場合、テーブルにある既存の行は更新され、追加したフィールドの列には設定した既定値が割り当てられます。これには、CURRENT_TIMESTAMP などの既定値が含まれます。

• この文で、列に NOT NULL 制約を設定せず、既定値を設定した場合は、既存のどの行の列でもデータは更新されません。これらの行に対する列の値は NULL です。

この既定の NOT NULL 制約の動作を変更するには、"SET OPTION" コマンドの "COMPILEMODE=NOCHECK" を参照してください。

“ID” という名前の通常のデータ・フィールドを指定し、RowID フィールドが既に “ID” (既定) という名前である場合、ADD COLUMN 操作は成功します。ALTER TABLE は ID データ列を追加し、RowId 列を “ID1” に名前変更して名前の重複を回避します。

ALTER COLUMN の制限

ALTER COLUMN では、単一の列の定義を変更できます。

• 構文 ALTER TABLE tablename ALTER COLUMN oldname RENAME newname を使用して列の名前を変更します。列の名前を変更すると、SQL フィールド名が変更されます。対応する永続クラスのプロパティ名は変更されません。ALTER COLUMN oldname RENAME newname は、トリガー・コードと ComputeCode の oldfield 名の参照を置き換えます。

• 列の特性 (データ型、既定値、NULL/NOT NULL、および照合タイプ) を変更します。

テーブルにデータが含まれている場合、データが含まれている列のデータ型を変更することでストリーム・データが非ストリーム・データになる場合、または非ストリーム・データがストリーム・データになる場合、そのデータ型変更は実行できません。これを実行しようとすると、SQLCODE -374 エラーが返されます。既存のデータがない場合は、このデータ型の変更が可能です。

ALTER COLUMN を使用して、フィールドの既定値を追加、変更、または削除できます。

テーブルにデータが含まれていて、列に NULL 値が含まれている場合は、その列に NOT NULL を指定することはできません。これは SQLCODE -305 エラーになります。

データを含む列の照合タイプを変更する場合は、その列のすべてのインデックスを再構築する必要があります。

MODIFY column の制限

MODIFY では、単一の列またはコンマ区切りの列のリストの定義を変更できます。

• 構文 ALTER TABLE tablename MODIFY oldname RENAME newname を使用して列の名前を変更します。列の名前を変更すると、SQL フィールド名が変更されます。対応する永続クラスのプロパティ名は変更されません。MODIFY oldname RENAME newname は、トリガー・コードと ComputeCode の oldfield 名の参照を置き換えます。

• 列の特性 (データ型、既定値、および他の特性) を変更します。

テーブルにデータが含まれている場合、以下のように、データが含まれている列のデータ型を互換性のないデータ型に変更することはできません。

• 既存のデータ値と競合する場合、より低い (より包括的ではない) データ型の優先順位を持つデータ型。これを実行しようとすると、SQLCODE -104 エラーになり、どのフィールドとどのデータ値がエラーの原因となっているかを示す %msg が表示されます。

• 既存のデータ値と競合する場合、より小さい MAXLEN、または MAXVAL/MINVAL を持つデータ型。これを実行しようとすると、SQLCODE -104 エラーになり、どのフィールドとどのデータ値がエラーの原因となっているかを示す %msg が表示されます。

• ストリーム・データ型から非ストリーム・データ型、または非ストリーム・データ型からストリーム・データ型へのデータ型の変更。これを実行しようとすると、SQLCODE -374 エラーが返されます。既存のデータがない場合は、このデータ型の変更が可能です。

MODIFY を使用して、フィールドの既定値を追加または変更できます。MODIFY を使用して、フィールドの既定値を削除することはできません。

テーブルにデータが含まれていて、列に NULL 値が含まれている場合は、その列に NOT NULL を指定することはできません。これは SQLCODE -305 エラーになります。構文形式 ALTER TABLE mytable MODIFY field1 NOT NULL および ALTER TABLE mytable MODIFY field1 CONSTRAINT nevernull NOT NULL は同じ操作を実行します。オプションの CONSTRAINT identifier 節は、互換性を保つための空命令です。InterSystems IRIS ではこのフィールド制約名を保持または使用しません。このフィールド制約名を指定してこのフィールド制約を削除しようとすると、SQLCODE -315 エラーになります。

DROP COLUMN の制限

DROP COLUMN は、コンマ区切りリストとして指定された複数の列定義を削除します。リストされる各列名の後には、その RESTRICT または CASCADE (指定しない場合、既定は RESTRICT)、および %DELDATA または %NODELDATE (指定しない場合、既定は %NODELDATA) オプションが続く必要があります。

既定では、列定義を削除しても、その列に格納されているデータはデータマップから削除されません。列定義とそのデータの両方を削除するには、%DELDATA オプションを指定します。

列の定義を削除しても、対応する列レベルの特権は削除されません。例えば、その列でデータを挿入、更新、または削除するためにユーザに与えられた特権です。これは、以下のような影響があります。

• 列が削除され、同じ名前を持つ別の列が追加されると、ユーザとロールは新しい列でも古い列で持っていたのと同じ特権を持ちます。

• 列が削除されても、その列のオブジェクト特権を削除することはできません。

この理由により、通常は列定義を削除する前に、REVOKE コマンドを使用して、列レベルの特権を削除することをお勧めします。

RESTRICT キーワード (またはキーワードなし) ： 列がインデックスで使用されている場合や、外部キー制約または他の一意制約で定義されている場合、その列を削除することはできません。その列を削除しようとすると、SQLCODE -322 エラーが発生して失敗します。既定は RESTRICT です。"DROP INDEX" を参照してください。

CASCADE キーワード ： 列がインデックスで使用されている場合は、インデックスが削除されます。複数のインデックスが存在する場合があります。列が外部キーで使用されている場合は、外部キーが削除されます。複数の外部キーが存在する場合があります。

列が COMPUTECODE 節内または COMPUTEONCHANGE 節内で使用されている場合は、その列を削除することはできません。これを実行しようとすると、SQLCODE -400 エラーが返されます。

ADD CONSTRAINT の制限

制約をフィールドのコンマ区切りリストに追加できます。例えば、UNIQUE (FName,SurName) 制約を追加できます。この制約は、2 つのフィールド FName と Surname の値を組み合わせて UNIQUE 制約を設定します。同様に、フィールドのコンマ区切りリストに対して主キー制約や外部キー制約を追加することもできます。

制約は名前付き制約にも名前のない制約にもできます。名前のない制約の場合、制約名はテーブル名を使用して生成されます。例えば、MYTABLE_Unique1 や MYTABLE_PKEY1 のようになります。

以下の例では、名前のない制約を 2 つ作成して、一意制約と主キー制約の両方をフィールドのコンマ区切りリストに追加します。

 ALTER TABLE SQLUser.MyStudents 
    ADD UNIQUE (FName,SurName),PRIMARY KEY (Fname,Surname)  

• 制約で使用するフィールドが存在している必要があります。存在しないフィールドを指定すると、SQLCODE -31 エラーが生成されます。

• 制約で RowId フィールドを使用することはできません。RowId (ID) フィールドを指定すると、SQLCODE -31 エラーが生成されます。

• 制約でストリーム・フィールドを使用することはできません。ストリーム・フィールドを指定すると、SQLCODE -400 “エラーインデックス属性に誤り” が生成されます。

• 制約は 1 つのフィールドに一度だけ適用できます。1 つのフィールドに同じ制約を 2 回指定すると、SQLCODE -400 エラー “インデックス名が重複しています” が生成されます。

オプションの CONSTRAINT identifier キーワード節を使用して、名前付き制約を作成できます。名前付き制約は、有効な識別子である必要があります。制約名では、大文字と小文字は区別されません。これにより、将来的に使用できるように、制約の名前が指定されます。以下に例を示します。

 ALTER TABLE SQLUser.MyStudents 
    ADD CONSTRAINT UnqFullName UNIQUE (FName,SurName)  

複数の制約をコンマ区切りリストとして指定できます。制約名は最初の制約に適用され、その他の制約には既定の名前が適用されます。

制約名は、テーブルで一意である必要があります。1 つのフィールドに同じ制約名を 2 回指定すると、SQLCODE -400 エラー “インデックス名が重複しています” が生成されます。

ADD PRIMARY KEY の制限

主キーの値は必須かつ一意です。したがって、主キー制約を既存のフィールドにまたはフィールドの組み合わせに追加すると、これらの各フィールドは必須フィールドになります。既存のフィールドのリストに主キー制約を追加する場合、これらのフィールドの値の組み合わせが一意である必要があります。NULL 値の入力が認められているフィールドには、主キー制約を追加できません。フィールド (またはフィールドのリスト) に一意でない値が含まれる場合、そのフィールド (またはフィールドのリスト) に主キー制約を追加することはできません。

既存のフィールドに主キー制約を追加する場合、フィールドが自動的に IDKey インデックスとして定義されることもあります。これはデータが存在するかどうか、および構成設定が以下のいずれかの方法で設定されているかどうかによります。

• SQL SET OPTION PKEY_IS_IDKEY 文

• システム全体の $SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッド構成オプション DDLPKeyNotIDKey。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、「DDLで作成された主キーはIDキーではありませんか」と表示されます。既定値は 1 です。

• 管理ポータルに進み、[システム管理]、[構成]、[SQL およびオブジェクトの設定]、[SQL] の順に選択します。[DDL を使用して作成されたテーブルの ID キーとして主キーを定義する] の現在の設定を表示します。

  • このチェック・ボックスにチェックが付いていない場合 (既定)、主キーはクラス定義の IDKey インデックスになりません。IDKEY ではない主キーを使用したレコードへのアクセスはかなり非効率的です。しかし、このタイプの主キー値は変更することができます。

  • このチェック・ボックスにチェックが付いている場合、主キー制約が DDL で指定され、フィールドにデータがないときには、主キー・インデックスが IDKey インデックスとしても定義されます。フィールドにデータがある場合、IDKey インデックスは定義されません。主キーを IDKey インデックスとして定義すると、データ・アクセスはより効率的ですが、一度設定された主キー値は変更できません。

CREATE TABLE でビットマップ・エクステント・インデックスを定義している場合に、後で ALTER TABLE を使用して IDKey でもある主キーを追加すると、システムは自動的にビットマップ・エクステント・インデックスを削除します。

主キーが既に存在する場合に主キーを作成する

主キーは 1 つしか定義できません。既定では、InterSystems IRIS は、主キーが既に存在する場合に主キーを定義しようとしたり、同じ主キーを 2 回定義しようとしたりすると、それを拒否して SQLCODE -307 エラーを発行します。主キーの第 2 定義が最初の定義と同じ場合も SQLCODE -307 エラーを発行します。現在の構成を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[既存キーに対して DDL の Create Primary Key を許可] 設定が表示されます。既定値は 0 (いいえ) で、これが推奨される構成設定です。このオプションが 1 (はい) に設定されていると、ALTER TABLE ADD PRIMARY KEY により、InterSystems IRIS は主キー・インデックスをクラス定義から削除し、指定の主キー・フィールドを使用したインデックスを再生成します。

管理ポータル、[システム管理]、[構成]、[SQL とオブジェクトの設定]、[SQL] から [冗長な DDL ステートメントを無視] チェック・ボックスにチェックを付けることにより、このオプション (および他の同様の作成、変更、および削除のオプション) をシステム全体で設定できます。

ただし、既存の主キーが存在する状態で主キーを作成できるようにこのオプションを設定していても、テーブルにデータがある場合は、IDKey インデックスを兼ねる主キー・インデックスは再作成できません。これを実行しようとすると、SQLCODE -307 エラーが生成されます。

ADD FOREIGN KEY の制限

外部キーの詳細は、CREATE TABLE コマンドの "外部キーの定義" と外部キーの "参照アクション節"、および "InterSystems SQL の使用法" の “外部キーの使用法” の章を参照してください。

既定では、同じ名前の 2 つの外部キーを持つことはできません。これを実行しようとすると、SQLCODE -311 エラーが生成されます。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[外部キーが存在する時に DDL ADD 外部キー制約を許可する] 設定が表示されます。既定値は 0 (いいえ) で、これが推奨設定です。1 (はい) の場合、同じ名前が存在しても、DDL を使用して外部キーを追加できます。

管理ポータル、[システム管理]、[構成]、[SQL とオブジェクトの設定]、[SQL] から [冗長な DDL ステートメントを無視] チェック・ボックスにチェックを付けることにより、このオプション (および他の同様の作成、変更、および削除のオプション) をシステム全体で設定できます。

テーブル定義では、同じ field-commalist フィールドを参照して相反する参照アクションを実行する、名前が異なる 2 つの外部キーを指定することはできません。同一のフィールドに対して相反する参照アクションを実行する 2 つの外部キーを定義した場合 (例： ON DELETE CASCADE と ON DELETE SET NULL)、InterSystems SQL は ANSI 標準に従い、エラーを生成しません。この代わりに InterSystems SQL では、DELETE 処理または UPDATE 処理でこのような相反する外部キー定義に遭遇したときにエラーを生成します。

存在しない外部キー・フィールドを ADD FOREIGN KEY で指定すると、SQLCODE -31 エラーが発生します。

存在しない親キー・テーブルを ADD FOREIGN KEY で参照すると、SQLCODE -310 エラーが発生します。既存の親キー・テーブルに存在しないフィールドを ADD FOREIGN KEY で参照すると、SQLCODE -316 エラーが発生します。親キー・フィールドを指定していない場合、既定の ID フィールドになります。

ADD FOREIGN KEY の発行前、ユーザは、参照されるテーブルまたは参照されるテーブルの列の REFERENCES 特権を持っている必要があります。REFERENCES 特権は、ダイナミック SQL または xDBC を介して ALTER TABLE を実行する場合に必要になります。

一意でない値を設定できるフィールド (またはフィールドの組み合わせ) を ADD FOREIGN KEY で参照すると、SQLCODE -314 エラーが発生して、%msg に追加の詳細が表示されます。

NO ACTION は、シャード・テーブルでサポートされている唯一の参照アクションです。

テーブルにデータがある場合は、ADD FOREIGN KEY は制約されます。この既定の制約の動作を変更するには、"SET OPTION" コマンドの "COMPILEMODE=NOCHECK オプション" を参照してください。

単一のフィールドのために ADD FOREIGN KEY 制約を定義し、外部キーが参照先テーブルの idkey を参照する場合は、InterSystems IRIS によって外部キー内のプロパティが参照プロパティに変換されます。この変換は、以下の制限に従います。

• テーブルにデータを含めることはできません。

• 外部キーに関するプロパティを永続クラスのプロパティにできません (つまり、既に参照プロパティにできません)。

• 外部キー・フィールドおよび参照先 idkey フィールドのデータ型とデータ型パラメータは同じである必要があります。

• 外部キー・フィールドを IDENTITY フィールドにすることはできません。

DROP CONSTRAINT の制限

既定では、外部キー制約によって参照されている一意キー制約または主キー制約は削除できません。これを実行しようとすると、SQLCODE -317 エラーが返されます。この既定の外部キー制約の動作を変更するには、"SET OPTION" コマンドの "COMPILEMODE=NOCHECK オプション" を参照してください。

主キー制約の削除による影響は、上記のように [主キーも ID キーである] 設定の内容によって異なります。

• PrimaryKey インデックスが IDKey インデックスを兼ねていない場合、主キー制約を削除すると PrimaryKey インデックスの定義が削除されます。

• PrimaryKey インデックスが IDKey インデックスを兼ねていて、テーブルにデータがない場合、主キー制約を削除するとインデックスの定義全体が削除されます。

• PrimaryKey インデックスが IDKey インデックスを兼ねていて、テーブルにデータがある場合、主キー制約を削除すると、IDKey インデックスの定義から PRIMARYKEY 修飾子のみが削除されます。

存在しない制約の削除

既定では、制約を持たないフィールドのフィールド制約を削除しようとした場合、InterSystems IRIS は削除を拒否し、SQLCODE -315 エラーを発行します。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[存在しない制約に対して DDL の DROP を許可] 設定が表示されます。既定値は 0 (いいえ) で、これが推奨設定です。このオプションが 1 (はい) に設定されていると、ALTER TABLE DROP CONSTRAINT により、InterSystems IRIS は処理を実行せず、エラー・メッセージを発行しません。

管理ポータル、[システム管理]、[構成]、[SQL とオブジェクトの設定]、[SQL] から [冗長な DDL ステートメントを無視] チェック・ボックスにチェックを付けることにより、このオプション (および他の同様の作成、変更、および削除のオプション) をシステム全体で設定できます。