ALTER TABLE 文

The ALTER TABLE 文により次のことが可能です。
  • 表に列を追加する。
  • 表に制約を追加する。
  • 表の列を破棄する。
  • 表にある制約を破棄する。
  • VARCHAR, CHAR VARYING, and CHARACTER VARYING 列の幅を増やす。
  • 表に定義された行レベルのロックを上書きする。(あるいは上書きされた内容を破棄する。)
  • 識別子列の増分値や開始値を変更する。
  • 列がNull可であるか否か変更する。
  • 列の規定値を変更する。

構文

ALTER TABLE 表名
{
    ADD COLUMN 列定義 |
    ADD CONSTRAINT節 |
    DROP [ COLUMN ] 列名 [ CASCADE | RESTRICT ]
    DROP { PRIMARY KEY | FOREIGN KEY 制約名 | UNIQUE 
	 制約名 | CHECK 制約名 | CONSTRAINT 制約名 }
    ALTER [ COLUMN ] 列定義変更 |
    LOCKSIZE { ROW | TABLE }
}

列定義

単純列名 データ型
[ 列単位制約 ]*
[ [ WITH ] DEFAULT 既定制約式 ]

既定制約式の詳細については、列の既定を参照してください。

列定義変更

列名 SET DATA TYPE VARCHAR(integer) |
column-name SET INCREMENT BY 定整数 |
column-name RESTART WITH 定整数 |
column-name [ NOT ] NULL |
column-name [ WITH ] DEFAULT 既定値

列定義変更のSET INCREMENT BY 定整数により、識別子列のとる連続した値の間隔が設定されます。識別子列がとる次の値は、最後にとられた値に増分が加算された値となります。この場合列にはあらかじめIDENTITY属性が定義されていなければなりません。

RESTART WITH 定整数により、識別子列がとる次の値が設定されます。RESTART WITHは、GENERATED BY DEFAULTが定義された、一意キーを持つ識別子列のある表に使うと便利です。GENERATED BY DEFAULTは明示的に値を挿入することおよび、暗黙的にシステムが発番した値を挿入することのいずれをも許すため、明示的に挿入された値がシステムが発番する値との間で競合する事がありえます。この競合を回避するために、RESTART WITHにより識別子列で次に発番される値を設定する事ができるのです。自動的に発番された値と手動で挿入された情報がある、次の例について考えてみてください。 
CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
CREATE UNIQUE INDEX tautoInd ON tauto(i)
INSERT INTO tauto(k) values 1,2
システムは自動的に識別子列に対して数値を発番します。しかしながら、あなたは手動で識別子列に幾つかの値を挿入しなければなりませんでした。 
INSERT INTO tauto VALUES (3,3)
INSERT INTO tauto VALUES (4,4)
INSERT INTO tauto VALUES (5,5)
識別子列にはこの時点で1から5の値があります。ここであなたがシステムに値を発番させると、システムは3という値を発番します。この値により一意キー例外が発生します。なぜなら3という値は既に手動で挿入されてしまっているからです。手動で値を挿入したので後処理として、識別子列に対してRESTART WITH 6 のALTER TABLE文を発行します。 
ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6

ALTER TABLE は表を参照するいかなるビューにも影響しません。これはSELECT一覧に"*"を持っていても然りです。新しく追加された列がビューから見えるようにする為には、ビューを破棄して作り直さなければなりません。

列の追加

追加する列の列定義は、構文の上でCREATE TABLE文の列と同じです。ですから、列の制約をALTER TABLE ADD COLUMN文の新しい列に書くこともできます。しかしながら、既存のテーブルにNOT NULL制約のある列を追加することは、その列に既定値を与えた場合のみ可能です。さもなければ、ALTER TABLE文が実行されたとき、例外が発生します。

CREATE TABLEと同様、列定義に一意あるいは主キー制約がある場合、列はnull値をとることができません。したがって、NOT NULL属性も必要です。(SQLSTATE 42831)

注: 表に明示的な列一覧が指定されないUPDATEトリガが定義されていた場合、その表に列を追加すると、追加された列はUPDATEトリガの暗黙的な更新列一覧に追加されます。さらに新しい列を拾うことができるよう、推移変数への全ての参照は無効となります。

制約の追加

ALTER TABLE ADD CONSTRAINTにより表ごとの制約が既存の表に追加されます。あらゆる種類の表ごとの制約がALTER TABLEで追加できます。ただし、既存の表への制約の追加には次の機能上の制限があります。
  • 外部キーやチェック制約が既存の表に追加されるときDerbyは既存の行が制約に違反しないか、表を調べます。もしいずれかの行が制約に違反する場合、Derbyは例外を投げて制約は追加されません。
  • 主キーに含まれるあらゆる列はnullでない値をとらなければならず、また一意でなければなりません。

    ALTER TABLE ADD UNIQUEやPRIMARY KEYは、単一の列による主キーを定義する簡単な方法を提供します。もしPRIMARY KEYがCという列の定義に書かれたされた場合、これはPRIMARY KEY(C)という別の節に書いたのと同じです。列はnullの値をとってはならないので、NOT NULL属性も指定されなければなりません。

制約の構文についての詳細は、CONSTRAINT 節を読んでください。 ADD TABLE ADD CONSTRAINTという書き方にて、制約を追加する場合は、表毎の制約の構文に従ってください。

列の破棄

ALTER TABLE DROP COLUMNにより、表から列を破棄できます。

COLUMNというキーワードは無くてもかまいません。

CASCADEやRESTRICTも、無くてかまいません。どちらも指定しなかった場合、既定はCASCADEです。

RESTRICTの場合、依存しているスキーマの情報が無効になるならば、列の破棄は失敗します。

CASCADEの場合、列の破棄と併せて、無効になるスキーマの情報も破棄されます。

DROP COLUMN RESTRICTが失敗する原因となるスキーマの情報には、次が含まれます。 ビュー、トリガ、主キー制約、外部キー制約、一意キー制約、チェック制約、列権限。破棄する列に依存するいずれかの情報があれば、DROP COLUMN RESTRICTは失敗します。

表にひとつしかない列は、破棄することができません。

sqlAuthorizationが真である場合、列を削除することはできません。(DERBY-1909を参照してください。) )

列が索引にて使われているか否かにかかわらず、列の破棄はCASCADE/RESTRICTのどちらでも同じように行われます。列が破棄されるとき、索引にあるその列が取り除かれます。その列が索引にあるただひとつの列であった場合、索引全体が破棄されます。

制約の破棄

ALTER TABLE DROP CONSTRAINTにより表の制約を破棄できます。名前付けしていない制約を破棄するためには、SYS.SYSCONSTRAINTSに記録されている自動生成された制約の名前をデリミトされた識別子として指定する必要があります。

主キー・一意性・外部キー制約を破棄すると、その制約が機能するために存在している索引が破棄されます。(この索引は支援索引とも呼ばれます。)

列の変更

列定義変更により、名前のある列を変更できます。可能な変更内容は以下のとおりです。
  • VARCHAR列の長さを増やすことができます。VARCHARというキーワードの代わりに、CHARACTER VARYINGやCHAR VARYINGを使うことができます。

    これらの列の幅を増やすためには、データ型と新しいサイズを列名の後に書きます。

    列の幅を減らしたり、データ型を変更したりする事はできません。主キーの一部や、外部キー制約から参照されている一意キー、外部キー制約の一部である列の幅は変更できません。

  • 識別子列のとる連続した値の間隔を指定することができます。

    識別子列のとる連続した値の間隔を設定するためには、定整数を与えます。あらかじめ列には識別子属性が与えられていなければなりません。(SQLSTATE 42837)もし表に既に行がある場合、列のSET INCREMENT defaultが加算された値は変更されません。

  • 列のNullへの制約の変更 列がNullをとる事への制約を変更できます。

    NOT NULL制約を既存の列に与えることができます。これを行う場合、既存のNULL値が表のその列に存在してはなりません。

    NOT NULL制約を既存の列から取り除くことができます。これを行う場合、その列がPRIMARY KEYやUNIQUEの制約にて使われている列であってはなりません。

  • 列の既定値を変更する

既定値の設定

新規の列に既定値を与えることができます。既定値とは値が指定されなかった場合に列に挿入される値です。もし明示的に既定値が与えられていなかった場合、列の既定値はNULLです。もし新しい列に既定値を与えた場合、表にある既存の行はその新列に既定値をとることとなります。

既定値のより詳細な情報については、CREATE TABLE 文を参照してください。

表のロック粒度の変更

もしあなたのシステムが既定の設定である行毎のロックを行うのであれば、LOCKSIZE節により表の行毎のロックを上書きすることができます。(一方あなたのシステムが表ごとのロックを行うよう設定されている場合、DerbyがLOCKSIZE節に対して例外を投げなかったとしても、ロックの粒度を行単位のロックに変更することはできません。) 表の行毎のロックを上書きするには、表にロックの設定を行います。 もし表が表毎のロックで作成された場合、ALTER TABLE文にてLOCKSIZE節を使うことで、その設定を行毎の設定に戻すことができます。これが便利な場合もあるのですが、その詳細については、Derbyのチューニングを参照してください。

例

-- 既存の表に列ごとの制約のある列を追加する。
-- 表に既存行があった場合、既存行の新列はNULLをとるので、
-- 例外が発生する。
ALTER TABLE CITIES ADD COLUMN REGION VARCHAR(26)
CONSTRAINT NEW_CONSTRAINT CHECK (REGION IS NOT NULL);

-- 既存の表に一意性制約を追加する。
-- 重複したキーがあった場合、例外が発生する。
ALTER TABLE SAMP.DEPARTMENT
ADD CONSTRAINT NEW_UNIQUE UNIQUE (DEPTNO);

-- Cities表に外部キー制約を追加する。
-- 行が制約を満たすか検証されて、
-- もし制約を満たさない行が見つかった場合、
-- 制約は追加されない。
ALTER TABLE CITIES ADD CONSTRAINT COUNTRY_FK
Foreign Key (COUNTRY) REFERENCES COUNTRIES (COUNTRY);

-- 主キー制約を表に追加する。
-- 最初に表が作成される。
CREATE TABLE ACTIVITIES (CITY_ID INT NOT NULL,
SEASON CHAR(2), ACTIVITY VARCHAR(32) NOT NULL);
-- もし主キーとした列にnullや重複した値がある場合、
-- この制約を追加することはできない。
ALTER TABLE Activities ADD PRIMARY KEY (city_id, activity);

-- もし依存する情報が無ければ、city_id列が破棄される。
ALTER TABLE Cities DROP COLUMN city_id RESTRICT;
-- 依存する情報と併せて、city_id列が破棄される。
ALTER TABLE Cities DROP COLUMN city_id CASCADE;

-- CITIES表の主キー制約を破棄する。
ALTER TABLE Cities DROP CONSTRAINT Cities_PK;
-- CITIES表の外部キー制約を破棄する。
ALTER TABLE Cities DROP CONSTRAINT COUNTRIES_FK;
-- 既定値を1にとるDEPTNO列を追加する。
ALTER TABLE SAMP.EMP_ACT ADD COLUMN DEPTNO INT DEFAULT 1;
-- VARCHAR型の列幅を増やす。
ALTER TABLE SAMP.EMP_PHOTO ALTER PHOTO_FORMAT SET DATA TYPE VARCHAR(30);
-- 表のロック粒度を変更する。
ALTER TABLE SAMP.SALES LOCKSIZE TABLE;

-- MANAGER列のNOT NULL制約を破棄する。
ALTER TABLE Employees ALTER COLUMN Manager NULL;
-- SSN列にNOT NULL制約を追加する。
ALTER TABLE Employees ALTER COLUMN ssn NOT NULL;

-- SALARY列の既定値を変更する。
ALTER TABLE Employees ALTER COLUMN Salary DEFAULT 1000.0

文の結果

ALTER TABLE 文により変更する表に依存する全ての文は、次回の実行にて再コンパイルされます。もし変更する表を参照する、開いたカーソルがある場合、ALTER TABLEは許可されません。

親トピック: 文