データ定義文は、テーブル、ビュー、インデックス、トリガーなどのデータベースオブジェクトを作成、変更、および削除するときに使用します。次のデータ定義文がサポートされています。
-
テーブル：
-
CREATE TABLE
-
ALTER TABLE
-
DROP TABLE
-
-
-
トリガー：
-
CREATE TRIGGERS
-
DROP TRIGGERS
CREATE TABLE
CREATE TABLE ステートメントでは、キーワード CREATE TABLE の後に新しいテーブルの名前を指定し、その後に列定義と列制約のリストを括弧で囲んで記述します。テーブル名には、識別子またはストリングを使用できます。
sql-statement ::= CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [database-name.] table-name
( column-def [, column-def]* [, constraint]* )
sql-statement ::= CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement
column-def ::= name [type] [[CONSTRAINT name] column-constraint]*
type ::= typename | typename ( number ) | typename ( number , number )
column-constraint ::= NOT NULL [ conflict-clause ] |
PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT] |
UNIQUE [conflict-clause] |
CHECK ( expr ) |
DEFAULT default-value |
COLLATE collation-name
constraint ::= PRIMARY KEY ( column-list ) [conflict-clause] |
UNIQUE ( column-list ) [conflict-clause] |
CHECK ( expr )
conflict-clause ::= ON CONFLICT conflict-algorithm
conflict-algorithm ::= ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
default-value ::= NULL | string | number | CURRENT_TIME | CURRENT_DATE | CURRENT_TIMESTAMP
sort-order ::= ASC | DESC
collation-name ::= BINARY | NOCASE
column-list ::= column-name [, column-name]*
各列定義は、列名とその列のデータ型、およびオプションで 1 つ以上の列制約を並べたものです。列のデータ型は、その列に格納できるデータを制限します。異なるデータ型の列に値を格納しようとすると、可能な場合にはランタイムによって値が適切な型に変換され、可能でない場合にはエラーが発生します。詳しくは、「データ型のサポート」を参照してください。
NOT NULL 列制約は、その列に NULL 値を格納できないことを示します。
UNIQUE 制約を指定すると、指定された 1 つまたは複数の列にインデックスが作成されます。このインデックスには、一意キーが含まれている必要があります。つまり、任意の行と任意の別の行の間で、指定された列の値、または指定された複数列の値の組み合わせが必ず異なっていなくてはなりません。CREATE TABLE ステートメントには複数の UNIQUE 制約を含めることができます。列定義内の複数の列に UNIQUE 制約を指定できるほか、複数テーブルレベルで UNIQUE 制約を設定することもできます。
CHECK 制約では、行のデータを挿入または更新する際の条件となる式を定義します。この式が true に評価された場合のみ、行のデータが挿入または更新されます。CHECK 制約の式の結果はブール値として解決される必要があります。
列定義内の COLLATE 句は、列のテキストエントリを比較するときに使用するテキスト照合関数を指定します。デフォルトでは、BINARY 照合関数が使用されます。COLLATE 句と照合関数について詳しくは、「COLLATE」を参照してください。
DEFAULT 制約では、INSERT の実行時に使用するデフォルト値を指定します。値は、NULL、ストリング定数、または数値にすることができます。また、状況に依存しない特殊なキーワード CURRENT_TIME、CURRENT_DATE、CURRENT_TIMESTAMP のいずれかを指定することもできます。値が NULL、ストリング定数、または数値の場合は、INSERT ステートメントで列の値が指定されていない場合に、これらがそのまま列に挿入されます。値が CURRENT_TIME、CURRENT_DATE、または CURRENT_TIMESTAMP の場合は、現在の UTC 日付と時刻の一方または両方が列に挿入されます。CURRENT_TIME の書式は HH:MM:SS です。CURRENT_DATE の書式は YYYY-MM-DD です。CURRENT_TIMESTAMP の書式は YYYY-MM-DD HH:MM:SS です。
PRIMARY KEY を指定すると、通常は対応する 1 つまたは複数の列に UNIQUE インデックスが作成されます。ただし、データ型が INTEGER（または int などのシノニムのいずれか）である単一の列に PRIMARY KEY 制約を指定すると、データベースではその列がテーブルの実際の主キーとして使用されます。これは、その列に一意の整数値しか格納できないことを意味します多くの SQLite 実装では、内部主キーとして使用するために指定できる列の型は、INTEGER だけです。ただし、Adobe AIR では、INTEGER のシノニム（int など）は同様にこの動作を指定できます。
テーブルに INTEGER PRIMARY KEY 列がない場合は、行が挿入されたときに整数キーが自動的に生成されます。行の主キーには、ROWID、OID、_ROWID_ のいずれかの特殊名を使用していつでもアクセスできます。これらの名前は、INTEGER PRIMARY KEY で明示的に宣言されているか、内部的に生成された値であるかに関係なく使用できます。ただし、テーブルに明示的な INTEGER PRIMARY KEY がない場合、結果データの列の名前は、特殊名ではなく、実際の列名になります。
INTEGER PRIMARY KEY 列には、キーワード AUTOINCREMENT も指定できます。AUTOINCREMENT キーワードを使用する場合、データベースは、列に明示的な値を指定しない INSERT ステートメントの実行時に、順番にインクリメントされる整数キーを自動生成して INTEGER PRIMARY KEY 列内に挿入します。
CREATE TABLE ステートメントには PRIMARY KEY 制約を 1 つだけ含めることができます。これは、ある列の定義の一部または単一のテーブルレベル PRIMARY KEY 制約にすることができます。主キー列は暗黙的に NOT NULL になります。
いくつかの制約の後にオプションの conflict-clause を続けると、その制約に対するデフォルトの制約競合解決アルゴリズムを指定できます。デフォルトは ABORT です。同じテーブル内の異なる制約に対して、異なるデフォルト競合解決アルゴリズムを指定することも可能です。INSERT ステートメントまたは UPDATE ステートメントで異なる競合解決アルゴリズムが指定された場合は、CREATE TABLE ステートメントで指定されたアルゴリズムの代わりにそのアルゴリズムが使用されます。詳しくは、
特殊な文と句
の「ON CONFLICT」を参照してください。
FOREIGN KEY 制約などの追加の制約を指定してもエラーにはなりませんが、実行時に無視されます。
TEMP または TEMPORARY キーワードが CREATE と TABLE の間にある場合、作成されるテーブルは、同じデータベース接続（SQLConnection インスタンス）内でのみ参照できます。データベース接続が終了すると、自動的に削除されます。一時テーブルでは、作成されたインデックスも一時的です。一時テーブルと一時インデックスは、メインのデータベースファイルとは別のファイルに格納されます。
オプションの database-name 接頭辞を指定すると、指定されたデータベース（データベース名を指定して attach() メソッドを呼び出すことによって SQLConnection インスタンスに接続されたデータベース）内にテーブルが作成されます。database-name 接頭辞と TEMP キーワードを両方とも指定すると、（database-name 接頭辞が temp である場合を除き）エラーになります。データベース名が指定されておらず、TEMP キーワードが存在しない場合は、メインデータベース（open() または openAsync() メソッドを使用して SQLConnection インスタンスに接続されたデータベース）にテーブルが作成されます。
列数またはテーブル内の制約数に恣意的な制限はありません。1 行のデータ量にも恣意的な制限はありません。
CREATE TABLE AS 形式は、クエリの結果セットでテーブルを定義します。結果に含まれる列の名前がテーブル列の名前になります。
オプションの IF NOT EXISTS 句があり、同じ名前の別のテーブルが既に存在する場合、データベースは CREATE TABLE コマンドを無視します。
テーブルは DROP TABLE ステートメントを使用して削除できます。また、ALTER TABLE ステートメントを使用して、限られた範囲で変更を加えることができます。
ALTER TABLE
ALTER TABLE コマンドを使用すると、テーブルの名前を変更したり、既存のテーブルに新しい列を追加したりできます。テーブルから列を削除することはできません。
sql-statement ::= ALTER TABLE [database-name.] table-name alteration
alteration ::= RENAME TO new-table-name
alteration ::= ADD [COLUMN] column-def
RENAME TO 構文は、[database-name.] table-name で指定されたテーブルの名前を new-table-name に変更します。このコマンドはあくまでも同じデータベース内でテーブル名を変更するためのもので、アタッチされたデータベース間でテーブルを移動することはできません。
名前変更されるテーブルにトリガーまたはインデックスがある場合、それらはテーブル名が変更された後もテーブルにアタッチされたままになります。ただし、名前変更されるテーブルを参照するトリガーによって実行されたビュー定義またはステートメントがある場合は、新しいテーブル名を使用するように自動的に変更されることはありません。名前変更されたテーブルにビューまたはトリガーが関連付けられている場合は、新しいテーブル名を使用してトリガーまたはビュー定義を手動で削除し、再作成する必要があります。
ADD [COLUMN] 構文は、既存のテーブルに新しい列を追加するときに使用します。新しい列は常に、既存の列リストの最後に追加されます。column-def 句では CREATE TABLE ステートメントで許可されるすべての形式を使用できますが、次のような制限があります。
-
列に PRIMARY KEY 制約または UNIQUE 制約を設定することはできません。
-
列のデフォルト値を CURRENT_TIME、CURRENT_DATE、または CURRENT_TIMESTAMP にすることはできません。
-
NOT NULL 制約を指定する場合は、列のデフォルト値を NULL 以外にする必要があります。
ALTER TABLE 文の実行時間は、テーブルに含まれるデータ量には関係ありません。
DROP TABLE
DROP TABLE ステートメントは、CREATE TABLE ステートメントで追加されたテーブルを削除します。削除するテーブルの名前を table-name で指定します。指定したテーブルは、データベースおよびディスクファイルから完全に削除されます。テーブルを復元することはできません。テーブルに関連付けられたインデックスもすべて削除されます。
sql-statement ::= DROP TABLE [IF EXISTS] [database-name.] table-name
デフォルトでは、DROP TABLE 文を実行してもデータベースファイルのサイズは小さくなりません。データベース内に空の領域が保持され、後続の INSERT 操作でその領域が使用されます。データベース内の空き領域を削除するには、SQLConnection.clean() メソッドを使用します。データベースが最初に作成されたときに autoClean パラメーターが true に設定された場合は、領域が自動的に解放されます。
オプションの IF EXISTS 句を指定すると、テーブルが存在しない場合に通常発生するエラーが抑制されます。
CREATE INDEX
CREATE INDEX コマンドでは、キーワード CREATE INDEX の後に新しいインデックスの名前、キーワード ON およびインデックスの作成対象となる作成済みのテーブルの名前を指定し、その後にインデックスキーに使用するテーブル内の列名のリストを括弧で囲んで記述します。
sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name.] index-name
ON table-name ( column-name [, column-name]* )
column-name ::= name [COLLATE collation-name] [ASC | DESC]
各列名の後にはソート順序を指定する ASC または DESC キーワードを続けることができますが、ソート順序の指定はランタイムでは無視されます。ソートは常に昇順で行われます。
各列の後の COLLATE 句は、その列のテキスト値で使用する照合順序を定義します。デフォルトの照合順序は、CREATE TABLE 文でその列に定義された照合順序です。照合順序が指定されていない場合は、BINARY 照合順序が使用されます。COLLATE 句と照合関数の定義については、「COLLATE」を参照してください。
1 つのテーブルにアタッチできるインデックス数に恣意的な制限はありません。インデックス内の列数にも制限はありません。
DROP INDEX
DROP INDEX 文は、CREATE INDEX 文で追加されたインデックスを削除します。指定したインデックスは、データベースファイルから完全に削除されます。インデックスを復元する唯一の方法は、適切な CREATE INDEX コマンドを再度発行することです。
sql-statement ::= DROP INDEX [IF EXISTS] [database-name.] index-name
デフォルトでは、DROP INDEX 文を実行してもデータベースファイルのサイズは小さくなりません。データベース内に空の領域が保持され、後続の INSERT 操作でその領域が使用されます。データベース内の空き領域を削除するには、SQLConnection.clean() メソッドを使用します。データベースが最初に作成されたときに autoClean パラメーターが true に設定された場合は、領域が自動的に解放されます。
CREATE VIEW
CREATE VIEW コマンドは、あらかじめ定義された SELECT ステートメントに名前を割り当てます。この新しく割り当てた名前は、別の SELECT ステートメントの FROM 句でテーブル名の代わりに使用できます。通常、ビューは、複雑な（および最近使用された）データのセットを他の操作で使用できる構造に結合することで、クエリーを単純化するために使用されます。
sql-statement ::= CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [database-name.] view-name AS select-statement
CREATE と VIEW の間に TEMP または TEMPORARY キーワードを指定すると、作成されたビューはデータベースを開いた SQLConnection インスタンスからのみ見えるようになり、データベースが閉じると自動的に削除されます。
[database-name] を指定すると、指定されたデータベース（name 引数を指定した attach() メソッドを使用して SQLConnection インスタンスに接続されたデータベース）内にビューが作成されます。[database-name] と TEMP キーワードを両方とも指定すると、（[database-name] が temp である場合を除き）エラーになります。データベース名が指定されておらず、TEMP キーワードが存在しない場合は、メインデータベース（open() または openAsync() メソッドを使用して SQLConnection インスタンスに接続されたデータベース）にビューが作成されます。
ビューは読み取り専用です。対応するタイプのトリガー（INSTEAD OF DELETE、INSTEAD OF INSERT、INSTEAD OF UPDATE）が少なくとも 1 つ定義されていない限り、ビューに対して DELETE ステートメント、INSERT ステートメント、または UPDATE ステートメントを使用することはできません。ビューに対するトリガーの作成について詳しくは、「CREATE TRIGGER」を参照してください。
ビューをデータベースから削除するには、DROP VIEW 文を使用します。
DROP VIEW
DROP VIEW ステートメントは、CREATE VIEW ステートメントで作成されたビューを削除します。
sql-statement ::= DROP VIEW [IF EXISTS] view-name
削除するビューの名前を view-name で指定します。ビューはデータベースから削除されますが、基になるテーブルのデータは変更されません。
CREATE TRIGGER
CREATE TRIGGER 文は、データベーススキーマにトリガーを追加するときに使用します。トリガーとは、指定されたデータベースイベント（database-event）が発生したときに自動的に実行されるデータベース操作（trigger-action）のことです。
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database-name.] trigger-name
[BEFORE | AFTER] database-event
ON table-name
trigger-action
sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database-name.] trigger-name
INSTEAD OF database-event
ON view-name
trigger-action
database-event ::= DELETE |
INSERT |
UPDATE |
UPDATE OF column-list
trigger-action ::= [FOR EACH ROW] [WHEN expr]
BEGIN
trigger-step ;
[ trigger-step ; ]*
END
trigger-step ::= update-statement |
insert-statement |
delete-statement |
select-statement
column-list ::= column-name [, column-name]*
トリガーは、特定のデータベーステーブルに対して DELETE、INSERT、または UPDATE が発生したときや、テーブルの指定された 1 つ以上の列に対して UPDATE が行われたときにその都度実行されるよう指定できます。トリガーは、TEMP または TEMPORARY キーワードが使用されていない限り永続的です。その場合、トリガーは、SQLConnection インスタンスのメインデータベース接続が終了すると削除されます。タイミング（BEFORE または AFTER）が指定されていない場合、デフォルトのタイミングは BEFORE になります。
FOR EACH ROW トリガーのみがサポートされているので、FOR EACH ROW キーワードはオプションです。FOR EACH ROW トリガーでは、WHEN 句の式が true に評価された場合、トリガーの起動元のステートメントによって挿入、更新または削除された各データベース行に対して、trigger-step ステートメントが実行されます。
WHEN 句を指定すると、WHEN 句が true に評価された行に対してのみ、trigger-steps として指定した SQL 文が実行されます。WHEN 句を指定しなければ、すべての行に対して SQL 文が実行されます。
トリガーの本文内で（trigger-action 句）、特別なテーブル名 OLD および NEW を使用して、影響を受けるテーブルの変更前および変更後の値を使用できます。OLD テーブルと NEW テーブルの構造は、トリガーを作成するテーブルの構造と一致します。OLD テーブルには、トリガーを発生させた文によって変更または削除された行の、操作が行われる前の状態が含まれます。NEW テーブルには、トリガーを発生させた文によって変更または作成された行の、操作が行われた後の状態が含まれます。WHEN 句と trigger-step ステートメントのどちらからも、NEW.column-name および OLD.column-name（column-name はトリガーが関連付けられているテーブルの列の名前）の形式の参照を使用して、挿入、削除、または更新される（された）行の値にアクセスできます。OLD テーブルと NEW テーブルの参照を使用できるかどうかは、トリガーが処理する database-event の種類によって決まります。
タイミングの指定（BEFORE、AFTER、または INSTEAD OF）は、trigger-step ステートメントが、関連する行の挿入時、変更時、または削除時を基準としていつ実行されるかを決定します。trigger-step 内の UPDATE ステートメントまたは INSERT ステートメントの一部として ON CONFLICT 句を指定することもできますが、トリガーを発生させた文の一部として ON CONFLICT 句が指定されている場合は、代わりにその競合処理ポリシーが使用されます。
テーブルのトリガーに加えて、INSTEAD OF トリガーをビューに対して作成できます。1 つ以上の INSTEAD OF INSERT、INSTEAD OF DELETE、または INSTEAD OF UPDATE トリガーがビューに定義されている場合は、対応するタイプのステートメント（INSERT、DELETE、または UPDATE）をビューで実行してもエラーとは見なされません。その場合は、INSERT、DELETE、または UPDATE をビューで実行すると、対応するトリガーが発生します。トリガーは INSTEAD OF トリガーなので、ビューの基礎となるテーブルは、トリガーが起動する原因となったステートメントによって変更されません。ただし、トリガーは、基礎となるテーブルに対して変更操作を実行するために使用できます。
INTEGER PRIMARY KEY 列を持つテーブルにトリガーを作成するときは、注意すべき重要な問題があります。トリガーを起動したステートメントによって更新される行の INTEGER PRIMARY KEY 列が BEFORE トリガーによって変更された場合は、更新が行われません。この問題を回避するには、INTEGER PRIMARY KEY 列の代わりに PRIMARY KEY 列を持つテーブルを作成します。
トリガーは DROP TRIGGER 文を使用して削除できます。テーブルまたはビューが削除されると、そのテーブルまたはビューに関連付けられたトリガーもすべて自動的に削除されます。
RAISE() 関数
特殊な SQL 関数である RAISE() は、トリガーの trigger-step ステートメントの中で使用できます。この関数の構文は次のとおりです。
raise-function ::= RAISE ( ABORT, error-message ) |
RAISE ( FAIL, error-message ) |
RAISE ( ROLLBACK, error-message ) |
RAISE ( IGNORE )
最初の 3 つの形式の 1 つがトリガーの実行中に呼び出されると、指定された ON CONFLICT 処理アクション（ABORT、FAIL、または ROLLBACK）が実行され、現在のステートメントの実行が終了します。ROLLBACK はステートメントの実行エラーと見なされるため、execute() メソッドが実行された SQLStatement インスタンスによって error（SQLErrorEvent.ERROR）イベントが送出されます。送出されたイベントオブジェクトの error プロパティ内にある SQLError オブジェクトの details プロパティは、RAISE() 関数で指定された error-message に設定されています。
RAISE(IGNORE) を呼び出すと、現在のトリガーの残りの部分、トリガーを発生させた文、および通常であれば引き続き実行されるはずの別のトリガーがすべて中止されます。データベースの変更はロールバックされません。トリガーを発生させた文自体がトリガーの一部である場合、そのトリガープログラムは次のステップの最初から再開されます。競合解決アルゴリズムについて詳しくは、「ON CONFLICT（競合アルゴリズム）」を参照してください。
DROP TRIGGER
DROP TRIGGER 文は、CREATE TRIGGER 文で作成されたトリガーを削除します。
sql-statement ::= DROP TRIGGER [IF EXISTS] [database-name.] trigger-name
トリガーはデータベースから削除されます。関連付けられたテーブルが削除されたときにも、トリガーは自動的に削除されます。
