[[FrontPage]]へ~
-[[リファレンスガイド]]へ~
--[[Firebird SQLリファレンス]]へ~
----
*Firebird SQL リファレンス:CREATE TABLE,DROP TABLE,DECLARE TABLE [#k236cb0b]
#contents
----
&aname(create_table);
*CREATE TABLE [#y37f90ce]
既存のデータベースに新しいテーブル(表)を作成します。 SQL,DSQLおよびisqlから使用出来ます。
この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの各リリースノートにおける追加内容に関して反映・統合されています。
**構文 [#s0fbfd79]
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>)&br;配列を宣言する場合最も外側のブラケットを含まなければなりません。例えば、次の文では、長さ6文字の文字列で構成される5×5の二次元配列になります。
my_array = varchar(6)[5,5]
--添え字の開始値が１以外の配列を指定するにはコロン(:)を使用してください。次の例では、10で始まり20で終わる整数型の配列となります。
my_array = integer[10:20]
--プレースホルダ(?)&br;SQL及びisqlでは、プレースホルダは使用できません。DSQLでは使用できます。
--変数&br;DQLおよびisqlでは、変数は使用できません。
--BLOB列に対するCOLLATE&br;BLOBの列に対してはCOLLATEを指定することは出来ません。
--引数に関する補足
|引数|説明|h
|table|作成するテーブル名。データベース内で一意な名前にする必要があります。|
|EXTERNAL [FILE] 'filespec'|データベース作成時、外部テーブルか外部ファイルがあることを宣言します。filespecで、外部ファイルまたはテーブルを(パス名で)指定します。|
|col|テーブルの列(カラム)の名前です。テーブル内で一意な名前にする必要があります。|
|datatype|列(カラム)のSQLデータタイプです。|
|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 UPDATE|REFERENCESと同時に使用されます。外部のキーが変更されたときの動作を指定します。次のオプションが設定できます。|
|~|・''NO ACTION'' : デフォルトです。変更は行われません。更新時に行われるプライマリーキーチェックで制約エラーとなるかもしれません。|
|~|・''CASCAD''E : ON DELETEでは、外部キーで合致するものを削除します。ON UPDATEでは、プライマリーキーに合わせて変更します。|
|~|・''SET NULL'' : 合致する外部キーの列にNULLをセットします。|
|~|・''SET DEFAULT'' : 影響をうける、外部のキーに合致するすべての列にデフォルト値を設定します。デフォルト値の設定が制約設定の後に行われた場合は、デフォルト値の設定は有効となりません。(ALTER TABLEで可能)|
|CHECK search_condition|チェック制約を指定します。指定した条件に合致しない値を設定することが出来なくなります。|
|COLLATE collation|デフォルトのソートで使用する、列のコレーションを指定します。|
**詳細の説明 [#n6944fa2]
CREATE TABLE は、現在接続中のデータベースに新しいテーブル(表)を作成すると同時に、列の定義や制約の設定を行います。テーブルを作成したユーザーは、テーブルの所有者となります。テーブルの所有者は、そのテーブルに対するすべての特権（他ユーザに対するGRANT、トリガ、ストアドプロシージャに関するものも含む）を所有します。
***列の定義に関して [#q3db0857]
-CREATE TABLE には、列の定義を行うためのいくつかのオプションがあります。
--ローカルな列の場合には、データタイプと名前とデータ型を指定します。
--計算型の列の場合には式を指定します。計算型の列では、その列がアクセスされた時に毎回列の値が計算されます。データ型が指定されていない場合は、データベースが自動的に適当な型を割当てます。計算型の列が定義される前に、式で使用される列項目は定義されていなければなりません。
--ドメインをベースとした列は、そのドメインでの設定をすべて継承します。また、同時に列に対する新しい(ドメイン定義での指定を上書きする) デフォルト値、NOT NULL属性、追加のチェック制約、コレーション指定を設定できます。また、同様に追加の列制限も可能です。
--CHAR,VARCHAR,BLOB(テキスト)などのテキスト用列には、キャラクタセットを指定することが出来ます。指定が無い場合は、(データベースで指定された) デフォルトのキャラクタセットが設定されます。&br; データベースのキャラクタセットが変更された場合、その後に定義された列は新しく設定されたキャラクタセットになります。しかし、すでに定義された列のキャラクタセットは変更されません。(訳注:つまり、作成済みのテーブルにはデフォルトキャラクタセットの変更は影響しないということ？)
--デフォルトキャラクタセットを指定しない場合、それはNONEと設定されます。 NONEが設定された場合、特定のキャラクタセットがないと仮定されます。つまり格納したデータがそのままの形で格納されます。&br; NONEと設定された列には、あるゆるキャラクタセットのデータが格納できます。しかし、そこにロードされたデータを、キャラクタセットが指定された別の列に対してロードすることは出来ません。このようなことを行った場合、文字変換が正しく行われずにエラーが発生するかもしれません。
--COLLATE節は、CHAR、VARCHAR、BLOB(テキスト)などのテキスト用列のためのコレーションオーダー(訳注:ソート時の順序に影響する)を指定します。指定は、列が"持っている"キャラクタセット(データベースのデフォルトキャラクタセットもしくは列の型定義時に指定したキャラクタセット)のみが指定できます。
-NOT NULL を指定すると、列に未知の値(NULL)を設定することが禁止されます。この指定は、すべての INSERT,UPDATE 操作に影響を与えます。
-''重要'' SQL埋め込みアプリケーションにおいて、同じプログラム中でテーブルを作成してデータを入力する場合は、DECLARE TABLE は CREATE TABLE に先行して実行されなければなりません。
-EXTERNAL FILE オプションは、データベース外のファイルからデータを入力してテーブルを作成します。このオプションは、以下のような場合に使用します。
--他のOSや、データベース以外のアプリケーションが出力したデータからテーブルを作成する場合。
--既存のデータベースアプリケーションのデータを外部ファイル経由で転送する場合。
***制約 [#oe9fa399]
テーブル作成時には、様々な制約を設定することが出来ます。制約は、テーブルと列(カラム)間、およびテーブル間にリレーションを張ることにより、入力データに規則性をもたらします。その規則は、データベースにアクセスするすべての作業に対して自動的に適用されます。
CREATE TABLE では、次のような制約を設定できます。
-PRIMARY KEY&br; PRIMARY KEY (プライマリーキー・主キー)は、１つまたはいくつかの列の内容が一意であることが保証される制約です。(訳注:つまり、指定列で同じ内容の列をもつ行が存在しないことを保証)&br; PRIMARY KEYには、必ず NOT NULL 属性を設定する必要があります。また、１テーブルには１セットのプライマリーキー(主キー)が設定できます。
-UNIQUE&br; UNIQUE キーが保証するのは、指定された列項目で同じ値を持った行が２つ以上存在しないということです。これも必ず NOT NULL 属性を設定する必要があります。１テーブルで、１つ以上の UNIQUE キーを持つことが出来ます。UNIQUE キーが指定された列は、他のテーブルのFOREIGN KEYによる参照を受け入れることが出来ます。
-参照制限(REFERENCES)&br; 参照制限が保証するのは、指定された列(外部キー・FOREIGN KEYで知られる)の値が、他テーブルの UNIQUE または PRIMARY KEY である列を参照して有効性がチェックされるということです。あるテーブルに参照制限が設定される以前に、その参照先テーブルの列に対しての、UNIQUE または PRIMARY KEY の指定が行われている必要があります。&br; REFERENCESは、参照するプライマリーキーがアップデートや削除が行われた時の処理方法の指定を、ON UPDATE や ON DELETE により行うことが出来ます。&br; ON UPDATE,ON DELETE で指定できる値は次のとおりです。
|動作指定|外部キーへの影響|h
|NO ACTION|デフォルトです。変更は行われません。更新や削除で行われるプライマリーキーチェックで制約エラーとなるかもしれません。|
|CASCADE|ON DELETEでは、外部キーで合致するものを削除します。ON UPDATEでは、プライマリーキーに合わせて変更します。|
|SET NULL|合致する外部キーのすべての列にNULLをセットします。|
|SET DEFAULT|影響をうける、外部のキーに合致するすべての列にデフォルト値を設定します。デフォルト値の設定が制約設定の後に行われた場合は、デフォルト値の設定は有効となりません。|
-権限について&br; テーブルの所有者が REFERENCES 特権を与えたユーザーのみ(所有者は最初から持っている)が、FOREIGN KEY(外部キー)を作成することが出来ます。外部キーが設定されたテーブルを更新するすべてのユーザーは、参照するテーブルに対する SELECT または REFERENCES 特権を所有していなければなりません。
-CHECK&br; チェック制限(CHECK)を設定することにより、search_condition で指定した条件が真の場合にのみ、テーブルへのインサートまたはアップデートが行われるようになります。search_condition には、値の範囲指定や他の列との比較などを組合わせて指定できます。&br;※ search_condition 中の値で USER を使用することにより、テーブルに書こうとするユーザー名を参照できます。
-プライマリーキー作成時の排他アクセスの必要性&br; PRIMARY KEY と FOREIGN KEY 制約を設定する場合には、データベースへの排他的アクセスが必要です。
-無名の制約の管理&br; 無名の制約の場合、システムは、システムテーブルの RDB$RELATION_CONSTRAINTS に格納される一意な名前を割当てます。
**用例 [#q71405f1]
-プライマリーキー(主キー)付きの単純なテーブル作成です。&br; 列の定義にドメイン 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制約付きの例です。&br; プライマリーキーは３つの列を指定しています。外部参照先のテーブルは、上例で作成した 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)
);
-外部参照の例です。&br; テーブルF2の列F2は、テーブルT1のプライマリーキーである列P1を参照する外部キーです。&br; (ON UPDATE CASCADE 指定により)テーブルT1の行に変更があった場合には、関係のあるすべてのテーブルT2の行に対して影響を及ぼします。また、(ON DELETE SET NULL 指定により)テーブルT1の行が削除された場合、関係するすべてのテーブルT2の列F2に、NULLがセットされます。&br;※用例のために外部参照の列名を意図的に変えてあるようですが、通常は混乱防止のために外部参照制約では同じ列名をつける場合が多いです。 (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
);
-計算型の列の例です。&br; 列名 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)
);
-キャラクタセット指定の例です。&br; 列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
);
**参照 [#ya683a36]
CREATE DOMAIN, [[DECLARE TABLE>#decrare_table]] , GRANT , REVOKE
これ以上の情報が欲しい場合には、Data Definition Guideの creating metadata, using integrity constraints, external tables, datatypes, キャラクタセット・データ型・権限を参照して下さい。
----
&aname(drop_table);
*DROP TABLE [#ifdaadac]
データベースからテーブル(表)を削除します。 SQL,DSQLおよびisqlから使用出来ます。
この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの各リリースノートにおける追加内容に関して反映・統合されています。
**構文 [#afcee5b8]
DROP TABLE name;
|引数|説明|h
|name|削除したいテーブルの名前|
※ DSQLで使用する場合、終端文字のセミコロンは不要です。 isql及びC/C++の埋め込みSQLでは行の終わりを示すために終端文字は必要です。
**詳細の説明 [#s5b4a866]
DROP TABLE は、データベースからテーブルのデータ、メタデータ、インデックスを削除します。また、テーブルを参照するすべてのトリガも削除します。ただし、参照中のSQL、ビュー、制約、ストアドプロシージャがある場合には、削除を行うことは出来ません。
また、外部テーブルに対して DROP TABLE を実行した場合、データベース上のテーブル定義は削除されますが、外部ファイルは削除しません。
テーブルの削除が行えるのは、テーブルの作成者、SYSDBAユーザー、およびOSの管理者権限を持ったユーザーのみです。
**用例 [#s83a6b75]
DROP TABLE COUNTRY;
**参照 [#sdefbbae]
ALTER TABLE , [[CREATE TABLE>#create_table]]
----
&aname(declare_table);
*DECLARE TABLE [#gdbb58b5]
CREATE TABLEでテーブルを作成する前に、プりプロセッサ (gpreなど)に、テーブル構造を示します。SQLで使用できます。
**構文 [#mfc93bc2]
DECLARE table TABLE (<table_def>);
|引数|説明|h
|table|作成するテーブル名。データベース内で一意である必要があります。|
|table_def|テーブル構造の定義です。詳しくはCREATE TABLEを参照して下さい。|
**詳細の説明 [#wb7ab5a5]
DECLARE TABLE によって、gpreはテーブルの定義内容を格納します。同一プログラム中でテーブルの作成とデータの格納を行う場合、この構文を使用する必要があります。
宣言したテーブル名がすでにデータベースに存在する場合や文法エラーなどがあった場合には、gpreはエラーを返します。
アプリケーションの実行中にテーブル参照を行った場合、列の定義やデータタイプがデータベース内のものと照合されます。テーブルがない場合や、定義内容が異なる場合には、アプリケーションでエラーが発生します。
DECLARE TABLE は、既存のドメインを使用することが出来ますが、その場合にはコンパイルを行う前にドメインが定義されていなければなりません。ドメインの定義が事前にない場合、列の定義を完全な形で行わなければなりません。
DECLARE TABLE 中の列定義には、制約を含めることは出来ません。たとえ、後に行う CREATE TABLE で制約を使用している場合も同様です。
''重要'' DECLARE TABLE は、複数のデータベースをアクセスするプログラムでは使用できません。
**用例 [#af7b42ab]
埋め込み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)
);
**参照 [#o617d557]
CREATE DOMAIN , [[CREATE TABLE>#create_table]]
----
&aname(recreate_table);
*RECREATE TABLE [#ued4b122]
既存のデータベースに、テーブル(表)を作成します。既存のテーブルを削除しなくても作成できますが、テーブルのデータはすべて失われますので注意して下さい。
構文は、[[CREATE TABLE>#create_table]] と同じです。
![[Firebird Wiki]](http://firebirdwiki.jp/image/firebirdwiki.png)