ALTER TABLE schema.TableName RENAME NewTableName
この操作では、既存のスキーマ内にある既存のテーブルの名前を変更します。変更できるのはテーブル名だけです。テーブル・スキーマは変更できません。NewTableName でスキーマ名を指定すると、SQLCODE -1 エラーになります。古いテーブルと新しいテーブルの両方に同じテーブル名を指定すると、SQLCODE -201 エラーが生成されます。
テーブルの名前を変更すると、SQL テーブル名が変更されます。対応する永続クラス名は変更されません。
テーブルの名前を変更しても、トリガ内にある古いテーブル名への参照は変更されません。
ビューで既存のテーブル名を参照している場合、そのテーブルの名前を変更しようとすると失敗します。その理由は、テーブルの名前の変更を試行するのはビューのリコンパイルを引き起こすアトミックな操作であるためで、これによって SQLCODE -30 エラー “テーブル 'schema.oldname' が見つかりません” が生成されます。
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 TABLE tablename ADD COLUMN 文を使用してテーブルに整数カウンタ・フィールドを追加しようとすると、以下のようになります。
-
テーブルに IDENTITY フィールドが存在しない場合、テーブルに IDENTITY フィールドを追加できます。テーブルに IDENTITY フィールドが既に存在する場合、ALTER TABLE 操作は SQLCODE -400 エラーで失敗し、%msg は "エラー #5281: クラスに複数のidentityプロパティが定義されています: 'Sample.MyTest::MyIdent2'" になります。ADD COLUMN を使用してこのフィールドを定義する場合、対応する RowID 整数値を使用して InterSystems IRIS はこのフィールドで既存データ行を入力します。
CREATE TABLE でビットマップ・エクステント・インデックスを定義して、後でテーブルに IDENTITY フィールドを追加した場合、IDENTITY フィールドが MINVAL が 1 以上の %BigInt、%Integer、%SmallInt、または %TinyInt のデータ型ではなく、テーブルにデータがないときは、システムは自動的にビットマップ・エクステント・インデックスを削除します。
-
テーブルに 1 つ以上の Serial (%Library.CounterOpens in a new tab) フィールドを追加できます。ADD COLUMN を使用してこのフィールドを定義する場合、このフィールドの既存のデータ行は NULL です。UPDATE を使用して、このフィールドの NULLである既存のデータ行に値を指定することができます。UPDATE を使用して非 NULL 値を変更することはできません。
-
テーブルに ROWVERSION フィールドが存在しない場合、ROWVERSION フィールドを追加できます。テーブルに ROWVERSION フィールドが既に存在する場合、ALTER TABLE 操作は SQLCODE -400 エラーで失敗し、%msg は "エラー #5320: クラス 'Sample.MyTest' にタイプ %Library.RowVersion のプロパティが複数あります。許されるのは 1 つだけです。プロパティ: MyVer,MyVer2" になります。ADD COLUMN を使用してこのフィールドを定義する場合、このフィールドの既存のデータ行は NULL です。NULL である ROWVERSION 値を更新することはできません。
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 ステートメントを無視] チェック・ボックスにチェックを付けることにより、このオプション (および他の同様の作成、変更、および削除のオプション) をシステム全体で設定できます。
例
以下の例では埋め込み SQL プログラムを使用して、テーブルを作成し、2 行を生成してからテーブルの定義を変更します。
この動作をはっきり示すために、最初の 2 つの埋め込み SQL プログラムは示されている順序で実行してください(埋め込み SQL では参照されるテーブルが既に存在していなければ INSERT 文をコンパイルすることができないため、ここでは 2 つの埋め込み SQL プログラムを使用する必要があります)。
DO $SYSTEM.Security.Login("_SYSTEM","SYS")
&sql(DROP TABLE SQLUser.MyStudents)
IF SQLCODE=0 { WRITE !,"Deleted table" }
ELSE { WRITE "DROP TABLE error SQLCODE=",SQLCODE }
&sql(CREATE TABLE SQLUser.MyStudents (
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL)
)
IF SQLCODE=0 { WRITE !,"Created table" }
ELSE { WRITE "CREATE TABLE error SQLCODE=",SQLCODE }
DO $SYSTEM.Security.Login("_SYSTEM","SYS")
NEW SQLCODE,%msg
&sql(INSERT INTO SQLUser.MyStudents (FirstName, LastName)
VALUES ('David','Vanderbilt'))
IF SQLCODE=0 { WRITE !,"Inserted data in table"}
ELSE { WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
&sql(INSERT INTO SQLUser.MyStudents (FirstName, LastName)
VALUES ('Mary','Smith'))
IF SQLCODE=0 { WRITE !,"Inserted data in table"}
ELSE { WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
以下の例は、ALTER TABLE を使用して ColorPreference 列を追加します。列定義で既定値を指定するため、システムではテーブルの既存の 2 つの行に対して ColorPreference に値 'Blue' を入力します。
NEW SQLCODE,%msg
&sql(ALTER TABLE SQLUser.MyStudents
ADD COLUMN ColorPreference VARCHAR(16) NOT NULL DEFAULT 'Blue')
IF SQLCODE=0 {
WRITE !,"Added a column",! }
ELSEIF SQLCODE=-306 {
WRITE !,"SQLCODE=",SQLCODE,": ",%msg }
ELSE { WRITE "SQLCODE error=",SQLCODE }
以下の例は、ALTER TABLE を使用して、2 つの計算列 FLName および LFName を追加します。既存の行のこれらの列に値はありません。その後に挿入される行については、これらの列それぞれに対して値が計算されます。
NEW SQLCODE,%msg
&sql(ALTER TABLE SQLUser.MyStudents
ADD COLUMN FLName VARCHAR(71) COMPUTECODE { SET {FLName}={FirstName}_" "_{LastName}}
COMPUTEONCHANGE (FirstName,LastName),
COLUMN LFName VARCHAR(71) COMPUTECODE { SET {LFName}={LastName}_ "," _{FirstName}}
COMPUTEONCHANGE (FirstName,LastName) )
IF SQLCODE=0 {
WRITE !,"Added two computed columns",! }
ELSE { WRITE "SQLCODE error=",SQLCODE }
関連項目