SQLiteチュートリアル(3)。データテーブルとビューの紹介

I. データテーブルの作成

    このコマンドの構文ルールと使い方は、基本的にほとんどのリレーショナルデータベースと同じなので、ここでは例としてSQLiteでテーブルを作成するための様々なルールを説明することに変わりはありません。しかし、いくつかの SQLite 固有のルールについては、補足説明をします。注意: 以下の例はすべて、sqlite に付属するコマンドラインツールで行っています。

    1). 最もシンプルなデータテーブル。

    ここで重要なのは、testtableのようなカスタムデータテーブルのテーブル名の場合、sqlite_で始めることはできないということです。この接頭辞で定義されたテーブル名はすべてsqliteの内部で使用されるからです。

    2). デフォルト値でデータテーブルを作成する場合。

    3). 指定されたデータベースにテーブルを作成します。

    ここでは、まず ATTACH DATABASE コマンドを使用して既存のデータベースファイルを現在の接続にアタッチし、次に mydb.testtable のようにデータベース名を指定してターゲットデータベースにデータテーブルを作成します。このルールに関する補足事項があります。一時テーブルを作成する必要がある場合、データベース名を指定する必要はありませんが、次の例を参照してください。
    -- 一時テーブルと通常テーブルの2つのテーブルを作成します。

    --現在の接続からローカルファイルにキャッシュされたデータをエクスポートし、現在の接続を終了します。

    -- sqliteへの接続を再確立し、先ほどエクスポートしたデータベースをマスターとして再インポートしてください。

    --このデータベースのテーブル情報を見ると、一時テーブルがデータベースファイルに永続化されていないことが結果でわかります。

    4). "IF NOT EXISTS"節。
    現在作成されているデータテーブルの名前がすでに存在する場合、つまり、すでに存在するテーブル名、ビュー名、およびインデックス名と競合する場合、この作成操作は失敗し、エラーが報告されます。しかし、テーブルが "IF NOT EXISTS" 節で作成された場合、現在のテーブル名がインデックス名と競合しない限り、この作成は何の効果もなく、つまり、エラーは発生しません。

    5). create table ... AS SELECT.
    このようにして作成されたデータ・テーブルは、SELECTクエリが返す結果セットと同じスキーマ情報を持ちますが、デフォルト値や主キーのような制約情報は含まれません。しかし、新しく作成されたテーブルには、結果セットから返されたすべてのデータが含まれます。

    .schemaコマンドはsqlite3コマンドラインツールの組み込みコマンドで、現在のデータテーブルのCREATE TABLEステートメントを表示します。   

    6). 主キー制約です。

    他のリレーショナル・データベースと同様、主キーは一意でなければならない。

    7). 一意性制約。

    ご覧のように、NULL値の挿入は両方とも成功しました。

    8). NULL (NOT NULL)制約です。

    出力からわかるように、first_colは非NULL制約で定義されているので、もうNULL値を挿入することはできません。   

    9). チェック可能性制約です。

II. テーブルの修正

    SQLite は ALTER TABLE コマンドを非常に限定的にサポートしており、テーブル名の修正と新しいフィールドの追加のみを行います。その他の機能、例えばフィールド名の変更、フィールドの削除、削除制約の追加などはサポートされています。

    1). テーブル名を修正する。

    まず、SQLiteでのテーブル名の変更は同一データベース内でのみ可能であり、Attachedデータベースへの移動はできないことに注意が必要です。また、一度テーブル名を変更すると、そのテーブルに既に存在するインデックスは影響を受けませんが、そのテーブルに依存するビューやトリガーは再定義する必要があります。

    .tablesコマンドの出力は、テーブルtesttableがtesttable2に変更されたことを示しています。   

    2). 新しいフィールドを追加しました。

    .schemaコマンドの出力は、テーブルtesttableの定義に追加されたフィールドがすでに含まれていることを示しています。   
    ALTER TABLE に関する最後の注意点は、SQLite におけるこのコマンドの実行時間は現在のテーブルの行数に影響されないということです。つまり、1000万行のデータを持つテーブルを修正するのと、1行のデータしかないテーブルを修正するのとでは、ほとんど同じ時間がかかるということです。
III. テーブルの削除

    SQLite でテーブルが削除されると、それに関連するインデックスとトリガーも一緒に削除されます。これは他の多くのリレーショナルデータベースでは不可能であり、関連するオブジェクトを削除しなければならない場合、テーブルの削除文にWITH CASCADE句を追加することによってのみ可能です。次の例を見てください。

    上の例からわかるように、削除されたテーブルが存在しない場合、SQLiteはエラーを報告し、エラーメッセージを出力します。例外を発生させずに実行したい場合は、CREATE TABLEと全く同じ意味を持つIF EXISTS句を追加すればよいのです。
IV. ビューを作成する。

    ここでは、簡単なSQLコマンドの例を挙げているだけです。一時ビュー、"IF NOT EXISTS" 節など、特定の意味や技術的な詳細については、上記のデータテーブルの作成セクションを参照することができます。
    1). 最もシンプルな見方。

    2). 一時的なビューを作成する。

    3). "IF NOT EXISTS"節。

V. ビューを削除する。

    この操作の構文は、基本的にテーブルの削除と同じなので、ここでは一例として紹介します。