FrontPageへ

Firebird SQL リファレンス:ALTER TABLE, CREATE DOMAIN, ALTER DOMAIN, DROP DOMAIN


ALTER TABLE

　テーブルに変更を加えます。列や制約の追加・削除・変更が行えます。 SQL,DSQL,isqlで使用できます。
　この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの 各リリースノートにおける追加内容に関して反映・統合されています。

構文 

ALTER TABLE table  [,  …];
 = {ADD 
    | ADD 
    | ALTER [COLUMN] column_name 
    | DROP col
    | DROP CONSTRAINT constraint}
 = {TO new_col_name
    | TYPE new_col_datatype
    | POSITION new_col_position}


列の定義:

 = col { | COMPUTED [BY] () | domain}
    [DEFAULT {literal | NULL | USER}]
    [NOT NULL] [] [COLLATE collation]


データタイプ:

 =
    {SMALLINT | INTEGER | BIGINT | FLOAT | DOUBLE PRECISION}
        []
    | {DATE | TIME | TIMESTAMP} []
    | {DECIMAL | NUMERIC} [(precision [, scale])] []
    | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
        [] [CHARACTER SET charname]
    | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
        [VARYING] [(int)] []
    | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
        [CHARACTER SET charname]
    | BLOB [(seglen [, subtype])]
 = [[x:]y [, [x:]y …]]


列制約:

 = [CONSTRAINT constraint]
    { UNIQUE
        | PRIMARY KEY
        | REFERENCES other_table [(other_col [, other_col …])]
            [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
            [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
        | CHECK ()
    }


表制約:

 = [CONSTRAINT constraint]
    { {PRIMARY KEY | UNIQUE} (col [, col …])
    | FOREIGN KEY (col [, col …]) REFERENCES other_table
        [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
        [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
    | CHECK ()}


 =   { | ()}
    |  [NOT] BETWEEN  AND 
    |  [NOT] LIKE  [ESCAPE ]
    |  [NOT] IN ( [,  …] | )
    |  IS [NOT] NULL
    |  {>= | <=}
    |  [NOT] {= | < | >}
    | {ALL | SOME | ANY} ()
    | EXISTS ()
    | SINGULAR ()
    |  [NOT] CONTAINING 
    |  [NOT] STARTING [WITH] 
    | ()
    | NOT 
    |  OR 
    |  AND 
 = {
       col [] | :variable
       |  |  | 
       | udf ([ [,  …]])
       | NULL | USER | RDB$DB_KEY | ?
    }
    [COLLATE collation]
 = num | 'string' | charsetname 'string'
 = COUNT (* | [ALL]  | DISTINCT )
    | SUM ([ALL]  | DISTINCT )
    | AVG ([ALL]  | DISTINCT )
    | MAX ([ALL]  | DISTINCT )
    | MIN ([ALL]  | DISTINCT )
    | CAST ( AS )
    | UPPER ()
    | GEN_ID (generator, )
 = {= | < | > | <= | >= | !< | !> | <> | !=}
 = １つの値を返すSQL式
 = １つの列・値を返すSELECT
 = １つの列で、0あるいは複数行のリストを返すSELECT
 = 0あるいは複数行の値のリストを返すSELECT



※　DSQLで使用する場合、終端文字のセミコロンは不要です。 isql及びC/C++の埋め込みSQLでは行の終わりを示すために終端文字は必要です。

  • 文法の補足
    　基本的には、CREATE TABLE と同様なことが 多いので、不明瞭な場合はそちらを参照して下さい。

  • BLOB列に対するCOLLATE
    　BLOBの列に対してはCOLLATEを指定することは出来ません。

  • 配列()
    　配列を宣言する場合最も外側のブラケットを含まなければなりません。 例えば、次の文では、長さ6文字の文字列で構成される5×5の二次元配列になります。 
    my_array = varchar(6)[5,5]
    　添え字の開始値が１以外の配列を指定するにはコロン(:)を使用してください。 次の例では、10で始まり20で終わる整数型の配列となります。 
    my_array = integer[10:20]

  • search_condition
    　search_conditionに関する詳細は、CREATE TABLE を参照して下さい。

  • 引数に関する補足

    ALTER TABLE の構文
    引数説明
    table変更を行う、既存のテーブル名です。
    operationテーブルに対する処理内容を指定します。以下のものが指定できます。
    ・ADD : 新しい列やテーブルの制約を追加します。
    ・DROP : 既存の列や制約を削除します。
    ・ALTER [COLUMN] : 列名やデータタイプ、位置を変更します。
    col_def新しい列を追加する場合には、以下を指定します。
    ・追加する列名とデータタイプは必須です。
    ・その他に指定可能な項目としては、デフォルト値、列の制約、コレーションオーダーがあります。
    col追加や削除する列の名前です。テーブル内で一意である必要があります。
    datatype列のデータタイプです。
    COMPUTED [BY] (expr)計算型の列であることを示します。exprで指定した式で必要なときに計算が行われるので、専用の記憶スペースを必要としません。
    ・exprは、宣言したデータタイプを得るためのあらゆる算術式を記述できます。
    ・exprの式内で使用できる列は、このテーブル内で事前に宣言されたものでなければなりません。
    ・exprではBLOB型の列を使用することは出来ません。
    ・exprは単一の値を得る式が使用できます。配列を返す式は使用できません。
    domain既存のドメイン名です。
    DEFAULT列のデフォルト値を指定します。次の値が設定できます。
    ・リテラル : 具体的な文字列、数値、またはデータ型
    ・NULL : NULL値
    ・USER : カレントユーザーのユーザー名。テキストを扱えるタイプのデータ型でのみ使用できます。
    ※列単位で指定されたデフォルト値設定は、ドメインで設定されたものを上書き(優先)します。
    CONSTRAINT constraint列(カラム)またはテーブル制約の名前。制約名は、テーブルの中で一意である必要があります。
    constraint_def列制約の指定を行います。有効なものは、UNIQUE、PRIMARY KEY、CHECK、およびREFERENCESです。
    REFERENCES列に参照制約(別テーブルを参照すること)を設定します。参照先の列名を明示しない場合、参照先に指定したテーブルにある同名の列を、データベースエンジンが自動的に探します。
    ON DELETE｜ON UPDATEREFERENCESと同時に使用されます。外部のキーが変更されたときの動作を指定します。次のオプションが設定できます。
    ・NO ACTION : デフォルトです。変更は行われません。更新時に行われるプライマリーキーチェックで制約エラーとなるかもしれません。
    ・CASCADE : ON DELETEでは、外部キーで合致するものを削除します。ON UPDATEでは、プライマリーキーに合わせて変更します。
    ・SET NULL : 合致する外部キーの列にNULLをセットします。
    ・SET DEFAULT : 影響をうける、外部のキーに合致するすべての列にデフォルト値を設定します。デフォルト値の設定が制約設定の後に行われた場合は、デフォルト値の設定は有効となりません。(ALTER TABLEで可能)
    NOT NULL列制約にNULL値禁止を指定します。
    ・テーブルの既存の行に追加される列は NOT NULL となりません。
    ・NOT NULL の指定は、列のアトリビュートのみで可能です。
    DROP CONSTRAINTテーブルの制約を削除します。
    table_constraint新しいテーブルの制約の指定です。制約は PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECKなどがあります。
    COLLATE collationデフォルトのソートで使用する、列のコレーションを指定します。


詳細の説明

　ALTER TABLE により、既存のテーブル(表)の構造を変更することが出来ます。 また、１つの ALTER TABLE 文により、複数の追加や削除を行えます。

  • テーブルに対して ALTER TABLE を実行できるのは、作成者、SYSDBAユーザー、 もしくは OS の管理者権限を持ったユーザーのみです。

  • 操作の指定が、テーブルのPRIMARY KEYやUNIQUE制約などに当たる場合、 ALTER TABLEは失敗します。例えば、次のような削除を行った場合です。
    • 指定列に UNIQUE,PRIMARY,FOREIGN KEY 制約がある場合。
    • 指定列が(訳注:他の列の)CHECK制約で使用されている場合。
    • 指定列が、評価式や計算型の列で使用されている場合。
    • 指定列が、ビューなど他のデータベースオブジェクトから参照されている場合。
  • 列の削除(DROP)を行った場合、格納されたデータはすべて失われます。

  • 参照制約に関して
    • FOREIGN KEY による参照制約においてデータの完全性維持を保証するために、 すべての REFERENCES 文に対して ON UPDATE や ON DELETE オプションを指定 しましょう。
    • ON UPDATE,ON DELETEの指定を行わなかった場合、列を削除する前に制約の削除や 計算型の列の削除を行わなければなりません。 FOREIGN KEY(外部キー)で参照されている PRIMARY KEY や UNIQUE 制約を削除する 前には、必ずそれらを参照している FOREIGN KEY を削除する必要があります。
    • テーブル所有者(作成者)の GRANT により、あるテーブルに対する REFFERENCES 特権を与えられているならば、そのテーブルに対する FOREIGN KEY による参照制約 を作成することが出来ます。他のユーザーが外部キーを設定されたテーブルを更新 する場合、参照するプライマリーなキーテーブルに対する REFERENCES もしくは SELECT 特権を持っている必要があります。
    • ドメイン指定にもCHECK制約を加えられますが、その変更にサブクエリを使用して いるものに対しCHECK制約を設定する場合、制約妨害を引き起こす可能性があります。
    • オプションで、列制限に名前を指定することが可能です。名前を指定しない場合 は、システムは、自動的生成される名前を割当てます。(訳注:システムテーブルの RDB$RELATION_CONSTRAINTS に格納される一意な名前を割当てます。) 制約に名前を指定しておけば、変更や削除により制約妨害のエラーメッセージに 名前が出るので、原因の特定が容易になります。

用例

すべて isql での例です。テーブルCOUNTRYに対して処理を行います。

  • テーブルに対する列の追加と削除を行っています。CURRENCY列のデータはすべて 失われます。
ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25),
  DROP CURRENCY;


  • ２つの列を追加していますが、片方には UNIQUE 制約を付与しています。
ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25) NOT NULL UNIQUE,
  ADD LARGEST_CITY VARCHAR(25) NOT NULL;


  • LARGEST_CITY 列の名前を BIGGEST_CITY に変更します。
ALTER TABLE COUNTRY ALTER LARGEST_CITY TO BIGGEST_CITY;


参照

　ALTER DOMAIN , CREATE DOMAIN , CREATE TABLE

　これ以上の情報が欲しい場合は、Embedded SQL Guide の altering tables の項を参照して下さい。





CREATE DOMAIN

　データベースでグローバルな、列定義を作成します。 SQL,DSQL,isql で使用できます。
　この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの 各リリースノートにおける追加内容に関して反映・統合されています。

構文 

CREATE DOMAIN domain [AS] 
    [DEFAULT {literal | NULL | USER}]
    [NOT NULL] [CHECK ()]
    [COLLATE collation];
 =
    {SMALLINT | INTEGER | BIGINT | FLOAT | DOUBLE PRECISION}
        []
    | {DATE | TIME | TIMESTAMP} []
    | {DECIMAL | NUMERIC} [(precision [, scale])] []
    | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
        [] [CHARACTER SET charname]
    | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
        [VARYING] [(int)] []
    | BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
        [CHARACTER SET charname]
    | BLOB [(seglen [, subtype])]
 = [[x:]y [, [x:]y …]]
 = {
    VALUE  value
    | VALUE [NOT] BETWEEN value AND value
    | VALUE [NOT] LIKE value [ESCAPE value]
    | VALUE [NOT] IN (value [, value …])
    | VALUE IS [NOT] NULL
    | VALUE [NOT] CONTAINING value
    | VALUE [NOT] STARTING [WITH] value
    | ()
    | NOT 
    |  OR 
    |  AND 
}
 = {= | < | > | <= | >= | !< | !> | <> | !=}


  • 文法の補足
    • BLOB列に対するCOLLATE
      　BLOBの列に対してはCOLLATEを指定することは出来ません。

    • 配列()
      　配列を宣言する場合最も外側のブラケットを含まなければなりません。 例えば、次の文では、長さ6文字の文字列で構成される5×5の二次元配列になります。 
      my_array = varchar(6)[5,5]
      　添え字の開始値が１以外の配列を指定するにはコロン(:)を使用してください。 次の例では、10で始まり20で終わる整数型の配列となります。 
      my_array = integer[10:20]



※　DSQLで使用する場合、終端文字のセミコロンは不要です。 isql及びC/C++の埋め込みSQLでは行の終わりを示すために終端文字は必要です。

引数説明
domainドメインのための一意な名前
datatypeSQLデータタイプ
DEFAULT列のデフォルト値を指定します。次の値が設定できます。
・リテラル : 具体的な文字列、数値、またはデータ型
・NULL : NULL値
・USER : カレントユーザーのユーザー名。テキストを扱えるタイプのデータ型でのみ使用できます。
NOT NULL列制約にNULL値禁止を指定します。
CHECK (dom_search_condition)ドメインに単体でのCHECK制約を作成します。
VALUEドメインの列名参照を表すプレースホルダです。
COLLATE collationデフォルトのソートで使用する、列のコレーションを指定します。


詳細の説明

　CREATE DOMAIN により、CREATE TABLE や ALTER TABLE で使用できる、継承可能な 列定義のテンプレートを作成します。ドメイン定義には、以下のものを含むことが 出来ます。

  • データ型
  • デフォルト値(オプション)
  • NULL値禁止(オプション)
  • CHECK制約(オプション)
  • コレーションオーダー(オプション)


　ドメイン定義での CHECK 制約は、ドメインに入力された値がdom_search_condition の条件で真のときに値が入力可能となります。CHECK制約は、他のドメインや列の値を 参照することは出来ません。
※ドメイン定義の中で制約と矛盾のあるものを作成しないように注意してください。 例えば、NOT NULL 制約があるのにデフォルト値が NULL であるものなどです。

　ドメイン定義で、CHAR や VARCHAR のようなテキスト用データ型の場合、 CHARACTER SET 節によりキャラクタセットの指定が出来ます。指定を行わなかった 場合、ドメインはデータベースのデフォルトキャラクタセットを使用します。

　デフォルトキャラクタセットを指定しない場合、それはNONEと設定されます。 NONEが設定された場合、特定のキャラクタセットがないと仮定されます。つまり 格納したデータがそのままの形で格納されます。
　NONEと設定された列には、あるゆるキャラクタセットのデータが格納できます。 しかし、そこにロードされたデータを、キャラクタセットが指定された別の列に 対してロードすることは出来ません。このようなことを行った場合、文字変換が 正しく行われずにエラーが発生するかもしれません。

　COLLATE節は、CHAR、VARCHAR、BLOB(テキスト)などのテキスト用列のための コレーションオーダー(訳注:ソート時の順序に影響する)を指定します。指定は、 列が"持っている"キャラクタセット(データベースのデフォルトキャラクタセット もしくは列の型定義時に指定したキャラクタセット)のみが指定できます。

　列の定義にドメインを使用した場合、どれはドメインのすべての特性を継承 します。そのとき、ドメインのデフォルト値、コレーション、NOT NULL の設定 は新しいものに変更(上書き)することが可能です。また、その列に対して追加の CHECK制約を設定することも出来ます。

用例

すべて isqlでの例です。

  • デフォルト値9999で、VALUE>1000というCHECK制約付きのドメインを作成します。 ここでのVALUEは、その列に設定しようとしている値を示すプレースホルダーです。 
    CREATE DOMAIN CUSTNO
  AS INTEGER
  DEFAULT 9999
  CHECK (VALUE > 1000);


  • 指定した４つの値以外は入らないドメインを作成します。 
    CREATE DOMAIN PRODTYPE
  AS VARCHAR(12)
  CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));


  • CHAR データ型の配列が定義されたドメインを作成します。 
    CREATE DOMAIN DEPTARRAY AS CHAR(31) [4:5];
  • 一連の動作のサンプルです。デフォルト値として現在のユーザー名が格納される ドメイン作成後に、そのドメインを使用してテーブルを作成、値を入力しています。 
    CREATE DOMAIN USERNAME AS VARCHAR(20) DEFAULT USER;

CREATE TABLE ORDERS (ORDER_DATE DATE, ENTERED_BY USERNAME,
  ORDER_AMT DECIMAL(8,2));

INSERT INTO ORDERS (ORDER_DATE, ORDER_AMT)
  VALUES ('1-MAY-93', 512.36);
    　INSERT の実行により、列 ENTERRED_BY には、データベースエンジンにより自動的 に現在のユーザー名が設定されます。現在のユーザー名が JSMITH であった場合、 SELECTでは次のような行が表示されるはずです。

    SELECT: 
    SELECT * FROM ORDERS;
    行表示: 
    1-MAY-93 JSMITH 512.36


  • TEXTサブタイプを持つBLOBのドメイン作成しています。キャラクタセットの指定も 行っています。 
    CREATE DOMAIN DESCRIPT AS
  BLOB SUB_TYPE TEXT SEGMENT SIZE 80
  CHARACTER SET SJIS;

参照

　ALTER DOMAIN , ALTER TABLE , CREATE TABLE , DROP DOMAIN

　これ以上の情報が欲しい場合は、Data Definition Guideの character set specification(キャラクタセット・データ型・権限) と、 collation orders を参照して下さい。





ALTER DOMAIN

　ドメインの定義内容を変更します。 SQL,DSQL,isql で使用できます。

構文 

ALTER DOMAIN name {
    SET DEFAULT {literal | NULL | USER}
    | DROP DEFAULT
    | ADD [CONSTRAINT] CHECK ()
    | DROP CONSTRAINT | new_col_name
    | TYPE datatype
};
 = {
    VALUE  value
    | VALUE [NOT] BETWEEN value AND value
    | VALUE [NOT] LIKE value [ESCAPE value]
    | VALUE [NOT] IN (value [, value …])
    | VALUE IS [NOT] NULL
    | VALUE [NOT] CONTAINING value
    | VALUE [NOT] STARTING [WITH] value
    | ()
    | NOT 
    |  OR 
    |  AND 
}
 = {= | < | > | <= | >= | !< | !> | <> | !=}


※　DSQLで使用する場合、終端文字のセミコロンは不要です。 isql及びC/C++の埋め込みSQLでは行の終わりを示すために終端文字は必要です。


引数説明
name既存のドメイン名です。
SET DEFAULT列のデフォルト値を指定します。次の値が設定できます。
・リテラル : 具体的な文字列、数値、またはデータ型
・NULL : NULL値
・USER : カレントユーザーのユーザー名。テキストを扱えるタイプのデータ型でのみ使用できます。
・デフォルト値設定は、設定されたものを上書き(優先)します。
DROP DEFAULT既存のデフォルト値指定を削除します。
ADD [CONSTRAINT] CHECK dom_search_conditionドメインに、CHECK 制約を追加します。ドメインには１つのCHECK制約のみを含むことが出来ます。
DROP CONSTRAINTドメイン定義からCHECK制約を削除します。
new_col_nameドメイン名を変更します。
TYPE data_typeドメインのデータ型を変更します。


詳細の説明

　ALTER DOMAIN により、既存ドメインの NOT NULL 設定などの内容を変更できます。 ドメインに対して行われる変更は、テーブルレベルで上書きされていない限り、 すべてのテーブルのドメインを指定した列に対して影響を及ぼします。
※ドメインのデータ型や NOT NULL の設定を変更した場合、ドメインは削除した後に 再作成と同様なことが行われます。

　ドメインの内容変更が出来るのは、作成者またはSYDBAユーザー、またはOSの管理者 権限を持ったユーザーのみです。

用例

isqlでの例です。作成したドメインのデフォルト値設定を 9,999 に変更しています。

CREATE DOMAIN CUSTNO AS INTEGER CHECK (VALUE > 1000);

ALTER DOMAIN CUSTNO SET DEFAULT 9999;

参照

　CREATE DOMAIN , CREATE TABLE , DROP DOMAIN

　すべての情報を得るためには、Data Definition Guide の、creating domains と using them to create column definitions を参照して下さい。





DROP DOMAIN

　データベースからドメインを削除します。 SQL,DSQL,isql で使用できます。

構文 

DROP DOMAIN name;


※　DSQLで使用する場合、終端文字のセミコロンは不要です。 isql及びC/C++の埋め込みSQLでは行の終わりを示すために終端文字は必要です。

引数説明
name既存のドメイン名


詳細の説明

　DROP DOMAIN により、既存のドメインをデータベースから削除します。

　ドメインが現在列の定義で使用されている場合、DROP 操作は失敗します。 このような場合は、ALTER TABLEによりドメインを使用している列を取り除いた後 に DROP DOMAIN を実行する必要があります。

　ドメインを削除が出来るのは、作成者またはSYDBAユーザー、またはOSの管理者 権限を持ったユーザーのみです。

用例

　isql でドメインの削除をします。

DROP DOMAIN COUNTRYNAME;


参照

　ALTER DOMAIN , ALTER TABLE , CREATE DOMAIN