CREATE TABLE

FrontPageへ

• リファレンスガイドへ
  
  • Firebird SQLリファレンスへ
    

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

CREATE TABLE

　既存のデータベースに新しいテーブル(表)を作成します。 SQL,DSQLおよびisqlから使用出来ます。 　この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの各リリースノートにおける追加内容に関して反映・統合されています。

構文

CREATE TABLE table [EXTERNAL [FILE] 'filespec']
   (<col_def> [, <col_def> | <tconstraint> …]);

列の定義:

<col_def> = col {<datatype> | COMPUTED [BY] (<expr>) | domain}
   [DEFAULT {literal | NULL | USER}]
   [NOT NULL]
   [<col_constraint>]
   [COLLATE collation]

データタイプ:

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

<array_dim> = [[x:]y [, [x:]y …]]

列制約:

<col_constraint> = [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 (<search_condition>)}

表制約:

<tconstraint> = [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 (<search_condition>)}

<search_condition> =
     <val> <operator> {<val> | (<select_one>)}
   | <val> [NOT] BETWEEN <val> AND <val>
   | <val> [NOT] LIKE <val> [ESCAPE <val>]
   | <val> [NOT] IN (<val> [, <val> …] | <select_list>)
   | <val> IS [NOT] NULL
   | <val> {>= | <=}
   | <val> [NOT] {= | < | >}
   | {ALL | SOME | ANY} (<select_list>)
   | EXISTS (<select_expr>)
   | SINGULAR (<select_expr>)
   | <val> [NOT] CONTAINING <val>
   | <val> [NOT] STARTING [WITH] <val>
   | (<search_condition>)
   | NOT <search_condition>
   | <search_condition> OR <search_condition>
   | <search_condition> AND <search_condition>

<val> = { col [<array_dim>] | :variable
   | <constant> | <expr> | <function>
   | udf ([<val> [, <val> …]])
   | NULL | USER | RDB$DB_KEY | ? }
       [COLLATE collation]

<constant> = num | 'string' | charsetname 'string'

<function> = COUNT (* | [ALL] <val> | DISTINCT <val>)
   | SUM ([ALL] <val> | DISTINCT <val>)
   | AVG ([ALL] <val> | DISTINCT <val>)
   | MAX ([ALL] <val> | DISTINCT <val>)
   | MIN ([ALL] <val> | DISTINCT <val>)
   | CAST (<val> AS <datatype>)
   | UPPER (<val>)
   | GEN_ID (generator, <val>)

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

<expr> = １つの値を返すSQL式

<select_one> = １つの列・値を返すSELECT

<select_list> = １つの列で、0あるいは複数行のリストを返すSELECT

<select_expr> = 0あるいは複数行の値のリストを返すSELECT

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

• 文法の補足
  • 配列(<array_dim>)
    配列を宣言する場合最も外側のブラケットを含まなければなりません。例えば、次の文では、長さ6文字の文字列で構成される5×5の二次元配列になります。

    my_array = varchar(6)[5,5]

• 添え字の開始値が１以外の配列を指定するにはコロン(:)を使用してください。次の例では、10で始まり20で終わる整数型の配列となります。

  my_array = integer[10:20]

• プレースホルダ(?)
  SQL及びisqlでは、プレースホルダは使用できません。DSQLでは使用できます。

• 変数
  DQLおよびisqlでは、変数は使用できません。

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

• 引数に関する補足

詳細の説明

　CREATE TABLE は、現在接続中のデータベースに新しいテーブル(表)を作成すると同時に、列の定義や制約の設定を行います。テーブルを作成したユーザーは、テーブルの所有者となります。テーブルの所有者は、そのテーブルに対するすべての特権（他ユーザに対するGRANT、トリガ、ストアドプロシージャに関するものも含む）を所有します。

列の定義に関して

• CREATE TABLE には、列の定義を行うためのいくつかのオプションがあります。

• ローカルな列の場合には、データタイプと名前とデータ型を指定します。

• 計算型の列の場合には式を指定します。計算型の列では、その列がアクセスされた時に毎回列の値が計算されます。データ型が指定されていない場合は、データベースが自動的に適当な型を割当てます。計算型の列が定義される前に、式で使用される列項目は定義されていなければなりません。

• ドメインをベースとした列は、そのドメインでの設定をすべて継承します。また、同時に列に対する新しい(ドメイン定義での指定を上書きする) デフォルト値、NOT NULL属性、追加のチェック制約、コレーション指定を設定できます。また、同様に追加の列制限も可能です。

• CHAR,VARCHAR,BLOB(テキスト)などのテキスト用列には、キャラクタセットを指定することが出来ます。指定が無い場合は、(データベースで指定された) デフォルトのキャラクタセットが設定されます。
  　データベースのキャラクタセットが変更された場合、その後に定義された列は新しく設定されたキャラクタセットになります。しかし、すでに定義された列のキャラクタセットは変更されません。(訳注:つまり、作成済みのテーブルにはデフォルトキャラクタセットの変更は影響しないということ？)

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

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

• NOT NULL を指定すると、列に未知の値(NULL)を設定することが禁止されます。この指定は、すべての INSERT,UPDATE 操作に影響を与えます。

• 重要　SQL埋め込みアプリケーションにおいて、同じプログラム中でテーブルを作成してデータを入力する場合は、DECLARE TABLE は CREATE TABLE に先行して実行されなければなりません。

• EXTERNAL FILE オプションは、データベース外のファイルからデータを入力してテーブルを作成します。このオプションは、以下のような場合に使用します。

• 他のOSや、データベース以外のアプリケーションが出力したデータからテーブルを作成する場合。
• 既存のデータベースアプリケーションのデータを外部ファイル経由で転送する場合。

制約

　テーブル作成時には、様々な制約を設定することが出来ます。制約は、テーブルと列(カラム)間、およびテーブル間にリレーションを張ることにより、入力データに規則性をもたらします。その規則は、データベースにアクセスするすべての作業に対して自動的に適用されます。 　CREATE TABLE では、次のような制約を設定できます。

• PRIMARY KEY
  　PRIMARY KEY (プライマリーキー・主キー)は、１つまたはいくつかの列の内容が一意であることが保証される制約です。(訳注:つまり、指定列で同じ内容の列をもつ行が存在しないことを保証)
  　PRIMARY KEYには、必ず NOT NULL 属性を設定する必要があります。また、１テーブルには１セットのプライマリーキー(主キー)が設定できます。

• UNIQUE
  　UNIQUE キーが保証するのは、指定された列項目で同じ値を持った行が２つ以上存在しないということです。これも必ず NOT NULL 属性を設定する必要があります。１テーブルで、１つ以上の UNIQUE キーを持つことが出来ます。UNIQUE キーが指定された列は、他のテーブルのFOREIGN KEYによる参照を受け入れることが出来ます。

• 参照制限(REFERENCES)
  　参照制限が保証するのは、指定された列(外部キー・FOREIGN KEYで知られる)の値が、他テーブルの UNIQUE または PRIMARY KEY である列を参照して有効性がチェックされるということです。あるテーブルに参照制限が設定される以前に、その参照先テーブルの列に対しての、UNIQUE または PRIMARY KEY の指定が行われている必要があります。
  　REFERENCESは、参照するプライマリーキーがアップデートや削除が行われた時の処理方法の指定を、ON UPDATE や ON DELETE により行うことが出来ます。
  　ON UPDATE,ON DELETE で指定できる値は次のとおりです。

• 権限について
  　テーブルの所有者が REFERENCES 特権を与えたユーザーのみ(所有者は最初から持っている)が、FOREIGN KEY(外部キー)を作成することが出来ます。外部キーが設定されたテーブルを更新するすべてのユーザーは、参照するテーブルに対する SELECT または REFERENCES 特権を所有していなければなりません。

• CHECK
  　チェック制限(CHECK)を設定することにより、search_condition で指定した条件が真の場合にのみ、テーブルへのインサートまたはアップデートが行われるようになります。search_condition には、値の範囲指定や他の列との比較などを組合わせて指定できます。
  ※　search_condition 中の値で USER を使用することにより、テーブルに書こうとするユーザー名を参照できます。

• プライマリーキー作成時の排他アクセスの必要性
  　PRIMARY KEY と FOREIGN KEY 制約を設定する場合には、データベースへの排他的アクセスが必要です。

• 無名の制約の管理
  　無名の制約の場合、システムは、システムテーブルの RDB$RELATION_CONSTRAINTS に格納される一意な名前を割当てます。

用例

• プライマリーキー(主キー)付きの単純なテーブル作成です。
  列の定義にドメイン COUNTRYNAME を作成して使用しています。

CREATE DOMAIN COUNTRYNAME AS VARCHAR(20);
CREATE TABLE COUNTRY (
 COUNTRY  COUNTRYNAME NOT NULL PRIMARY KEY,
 CURRENCY VARCHAR(10) NOT NULL
);

• 列レベル及びテーブルレベルの UNIQUE 制約付きの例です。

CREATE TABLE STOCK (
 MODEL     SMALLINT NOT NULL UNIQUE,
 MODELNAME CHAR(10) NOT NULL,
 ITEMID    INTEGER  NOT NULL,

 CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID)
);

• テーブルレベルのPRIMARY KEY,FOREIGN KEY,CHECK制約付きの例です。
  　プライマリーキーは３つの列を指定しています。外部参照先のテーブルは、上例で作成した COUNTRY テーブルとなっています。ドメインも使用しています。 VARCHARの配列となっている列(LANGUAGE_REQ)もあります。

CREATE TABLE JOB (
 JOB_CODE        JOBCODE NOT NULL,
 JOB_GRADE       JOBGRADE NOT NULL,
 JOB_COUNTRY     COUNTRYNAME NOT NULL,
 JOB_TITLE       VARCHAR(25) NOT NULL,
 MIN_SALARY      SALARY NOT NULL,
 MAX_SALARY      SALARY NOT NULL,
 JOB_REQUIREMENT BLOB(400,1),
 LANGUAGE_REQ    VARCHAR(15) [5],

 PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),
 FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY),
 CHECK (MIN_SALARY < MAX_SALARY)
);

• 外部参照の例です。
  　テーブルF2の列F2は、テーブルT1のプライマリーキーである列P1を参照する外部キーです。
  　(ON UPDATE CASCADE 指定により)テーブルT1の行に変更があった場合には、関係のあるすべてのテーブルT2の行に対して影響を及ぼします。また、(ON DELETE SET NULL 指定により)テーブルT1の行が削除された場合、関係するすべてのテーブルT2の列F2に、NULLがセットされます。
  ※用例のために外部参照の列名を意図的に変えてあるようですが、通常は混乱防止のために外部参照制約では同じ列名をつける場合が多いです。 (T2の列名を、T1に合わせてF2ではなくてP1にします) そのようにした場合、テーブル名のみを明記すれば良いことになっています。

CREATE TABLE T1 (
 P1 INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE T2 (
 F2 INTEGER FOREIGN KEY REFERENCES T1.P1

 ON UPDATE CASCADE
 ON DELETE SET NULL
);

• 計算型の列の例です。
  　列名 NEW_SALARY が計算型の列です。EMPNO,SALARYはどこかで定義したドメインです。また、CHANGE_DATEでは'NOW'によりデフォルト値が現在時間となるように指定しています。

CREATE TABLE SALARY_HISTORY (
 EMP_NO         EMPNO NOT NULL,
 CHANGE_DATE    DATE DEFAULT 'NOW' NOT NULL,
 UPDATER_ID     VARCHAR(20) NOT NULL,
 OLD_SALARY     SALARY NOT NULL,
 PERCENT_CHANGE DOUBLE PRECISION
   DEFAULT 0
   NOT NULL
   CHECK (PERCENT_CHANGE BETWEEN -50 AND 50),
 NEW_SALARY     COMPUTED BY
   (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),

 PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
 FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO)
);

• キャラクタセット指定の例です。
  　列BOOKNOは、データベースのデフォルトキャラクタセットになります。列TITLEも同様ですが、コレーションオーダーの指定を行っています。列EUROPUBでは、キャラクタセットとコレーションオーダーの両方を指定しています。

CREATE TABLE BOOKADVANCE (
 BOOKNO CHAR(6),
 TITLE CHAR(50) COLLATE ISO8859_1,
 EUROPUB CHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR
);

参照

CREATE DOMAIN, DECLARE TABLE , GRANT , REVOKE

　これ以上の情報が欲しい場合には、Data Definition Guideの creating metadata, using integrity constraints, external tables, datatypes, キャラクタセット・データ型・権限を参照して下さい。

DROP TABLE

　データベースからテーブル(表)を削除します。 SQL,DSQLおよびisqlから使用出来ます。 　この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの各リリースノートにおける追加内容に関して反映・統合されています。

構文

DROP TABLE name;

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

詳細の説明

　DROP TABLE は、データベースからテーブルのデータ、メタデータ、インデックスを削除します。また、テーブルを参照するすべてのトリガも削除します。ただし、参照中のSQL、ビュー、制約、ストアドプロシージャがある場合には、削除を行うことは出来ません。 　また、外部テーブルに対して DROP TABLE を実行した場合、データベース上のテーブル定義は削除されますが、外部ファイルは削除しません。

　テーブルの削除が行えるのは、テーブルの作成者、SYSDBAユーザー、およびOSの管理者権限を持ったユーザーのみです。

用例

DROP TABLE COUNTRY;

参照

ALTER TABLE , CREATE TABLE

DECLARE TABLE

　CREATE TABLEでテーブルを作成する前に、プりプロセッサ (gpreなど)に、テーブル構造を示します。SQLで使用できます。

構文

DECLARE table TABLE (<table_def>);

詳細の説明

　DECLARE TABLE によって、gpreはテーブルの定義内容を格納します。同一プログラム中でテーブルの作成とデータの格納を行う場合、この構文を使用する必要があります。 　宣言したテーブル名がすでにデータベースに存在する場合や文法エラーなどがあった場合には、gpreはエラーを返します。

　アプリケーションの実行中にテーブル参照を行った場合、列の定義やデータタイプがデータベース内のものと照合されます。テーブルがない場合や、定義内容が異なる場合には、アプリケーションでエラーが発生します。

　DECLARE TABLE は、既存のドメインを使用することが出来ますが、その場合にはコンパイルを行う前にドメインが定義されていなければなりません。ドメインの定義が事前にない場合、列の定義を完全な形で行わなければなりません。

　DECLARE TABLE 中の列定義には、制約を含めることは出来ません。たとえ、後に行う CREATE TABLE で制約を使用している場合も同様です。

　重要　DECLARE TABLE は、複数のデータベースをアクセスするプログラムでは使用できません。

用例

　埋め込みSQLで使用した例です。

EXEC SQL
DECLARE STOCK TABLE (
  MODEL     SMALLINT,
  MODELNAME CHAR(10),
  ITEMID    INTEGER
);

EXEC SQL
CREATE TABLE STOCK (
  MODEL     SMALLINT NOT NULL UNIQUE,
  MODELNAME CHAR(10) NOT NULL,
  ITEMID    INTEGER NOT NULL,
  CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID)
);

参照

CREATE DOMAIN , CREATE TABLE

RECREATE TABLE

　既存のデータベースに、テーブル(表)を作成します。既存のテーブルを削除しなくても作成できますが、テーブルのデータはすべて失われますので注意して下さい。

　構文は、CREATE TABLE と同じです。