« «
REPLACE
» »
SELECT

CREATE TABLE

2011. 02. 26
SQLiteによるSQLの解釈

CREATE TABLE

create-table-stmt:

create-table-stmt

column-def:

column-def

type-name:

type-name

column-constraint:

column-constraint

table-constraint:

table-constraint

foreign-key-clause:

foreign-key-clause

『CREATE TABLE』コマンドは、SQLiteデータベース内に新しいテーブルを生成します。

CREATE TABLEコマンドは、以下の新しいテーブルの属性を指定します。
  • 新しいテーブルの名前

  • 新しいテーブルを生成させるデータベース。
    テーブルは、メインデータベース、一時データベース、または接続されている全てのデータベース内に作成できます。

  • テーブル内の各列の名前

  • テーブル内の各列の宣言された型

  • テーブル内の各列へのデフォルト値、または式

  • 各列で使用するデフォルトの照合順序

  • オプションで、テーブルへのPRIMARY KEY。
    単一列や複合(複数列)のプライマリキーをサポートします。

  • 各テーブル用のSQLの制約の設定。
    SQLiteは、UNIQUE、NOT NULL、CHECK、FOREIGN KEY制約をサポートします。
全てのCREATE TABLEステートメントは、新しいテーブルの名前を指定する必要があります。

『sqlite_』で始まるテーブル名は、内部で使用するために予約されています。

『sqlite_』で始まる名前でテーブルを生成しようとするとエラーになります。

<database-name>が指定されている場合、attached databaseの名前を『main』または『temp』のどちらかに指定する必要があります。

この場合、新しいテーブルは指定されたデータベース内に生成されます。

『CREATE』と『TABLE』の間に『TEMP』または『TEMPORARY』キーワードがある場合、新しいテーブルは一時データベースに生成されます。

<database-name>とTEMPまたはTEMPORARYキーワードの両方を指定した場合、<database-name>が『temp』でない限りエラーとなります。

データベース名が指定されていなく、TEMPキーワードが存在しない場合、メインデータベースにテーブルが生成されます。

通常、同じ名前のインデックスやビューのテーブルが既に含まれているデータベース内に新しいテーブルを生成しようとするとエラーになります。

しかし、CREATE TABLEステートメントの一部に『IF NOT EXISTS』句が指定されていて、同じ名前のテーブルまたはビューが既に存在している場合、CREATE TABLEコマンドは単純に(エラーメッセージを返すこと無く)無効となります。

インデックスが既に存在しているために生成できなかった場合、『IF NOT EXISTS』句が指定されていてもエラーが返されます。

同じ名前のtriggerが存在するテーブルを生成してもエラーにはなりません。

テーブルはDROP TABLEステートメントを使用して削除されます。


CREATE TABLE ... AS SELECTステートメント

『CREATE TABLE ... AS SELECT』ステートメントは、SELECTステートメントの結果に基づいてデータベースにテーブルを生成します。

テーブルはSELECTステートメントによって返される行と同じ数の列を持ちます。

各列の名前は、SELECTステートメントの結果セット内の列に対応する同じ名前になります。

各列の宣言された型は、下表で示すようにSELECTステートメントの結果セット内にある対応する式の、expression affinityによって決定されます。

Expression AffinityColumn Declared Type
TEXT"TEXT"
NUMERIC"NUM"
INTEGER"INT"
REAL"REAL"
NONE""(空文字列) 

CREATE TABLE ASを使用して生成されたテーブルは、PRIMARY KEYやあらゆる種類の制約を持ちません。

各列のデフォルト値はNULLです。

新しいテーブルの各列へのデフォルトの照合順序はBINARYです。

CREATE TABLE ASを使用して生成されたテーブルは、最初にSELECTステートメントによって返されるデータの行が格納されます。

行はSELECTステートメントによって返される順序で、1から始まるrowid値を昇順で連番で割り当てられます。


列の定義

CREATE TABLE ... AS SELECTステートメントでない限り、CREATE TABLEには1つ以上のcolumn definitionsが含まれており、オプションでtable constraintsのリストが続きます。

各列の定義は、列の名前、オプションで列の宣言された型が続き、1つ以上のcolumn constraintsで校正されます。

前のステートメントの目的のため、『column constraints』の定義にはCOLLATEとDEFAULT句が含まれていますが、これらは実際には制約として解釈されず、テーブルに含まれているかもれしれないデータを制限しません。

その他の制約(NOT NULL、CHECK、UNIQUE、PRIMARY KEY、FOREIGN KEY制約)はテーブルデータに制約を課しており、下記のSQL Data Constraintsに記述されています。

ほとんどのSQLデータベースとは異なり、SQLiteは列の宣言された型に基づいた列に挿入するデータの型を制限しません。

代わりに、SQLiteはdynamic typingを使用します。

列の宣言された型は列のaffinityの決定のみに使用されます。

DEFAULT句は、INSERTを実行する時にユーザによって明示的に提供された値が無い場合、列に使用するデフォルト値を指定します。

列の定義に接続された明示的なDEFAULT句が無い場合、列のデフォルト値はNULLになります。

明示的なDEFAULT句のデフォルト値には、NULL、文字列定数、blob定数、符号付き数値、カッコで囲んだ定数式を指定します。

明示的なデフォルト値はまた、特別なcase-independentキーワードとしてCURRENT_TIME、CURRENT_DATE、CURRENT_TIMESTAMPのいずれかになります。

DEFAULT句の目的のため、式はサブクエリまたは二重引用符で囲んだ文字列定数を含まない定数が提供されていると見なされます。

全てのテーブルの列の明示的な値は、INSERTステートメントによってテーブルに行が挿入される度に提供はされず、以下のように新しい行に格納されている値はデフォルト値によって決定されます。
  • 列のデフォルト値が定数NULL、テキスト、blobまたは符号付き数値の場合、値は新しい行に直接使用されます。

  • 列のデフォルト値がカッコで囲んだ式の場合、挿入された各行のために一度式を計算し、その結果を新しい行に使用します。

  • 列のデフォルト値がCURRENT_TIME、CURRENT_DATE、CURRENT_DATETIMEの場合、値は現在のUTCの日時を表現したテキストを新しい行に使用します。
    CURRENT_TIMEの値のフォーマットは『HH:MM:SS』で、CURRENT_DATEは『YYYY-MM-DD』、CURRENT_TIMESTAMPは『YYYY-MM-DD HH:MM:SS』となります。
COLLATE句は、列のデフォルトの照合順序として使用する照合順序の名前を指定します。

COLLATE句が指定されていない場合、デフォルトの照合順序はBINARYです。

テーブルの列数は、コンパイル時の引数SQLITE_MAX_COLUMNによって制限されています。

テーブルの単一行は、SQLITE_MAX_LENGTHのバイト以上のデータを格納することはできません。

これら2つの制限は、C/C++インターフェイスのsqlite3_limit()を使用して、実行時に下げることができます。


SQLデータの制約

SQLiteの各テーブルは最大で1つのPRIMARY KEYを持つことができます。

キーワードPRIMARY KEYが列定義に追加されている場合、テーブルのプライマリキーが単一列で構成されます。

または、PRIMARY KEY句がtable-constraintとして指定されている場合は、テーブルのプライマリキーはPRIMARY KEY句の一部として指定された指定された列のリストで構成されます。

単一のCREATE TABLEステートメント内に1つ以上のPRIMARY KEY句がある場合はエラーになります。

テーブルが単一列のプライマリキーを持ち、列の宣言された型が『INTEGER』の場合、その列はINTEGER PRIMARY KEYとして知られています。

INTEGER PRIMARY KEYに関連する特別なプロパティや動作についての詳細は、下記を参照してください。

プライマリキーを持つテーブルの各行は、プライマリキー列内の値の特有な組み合わせを特徴としています。

プライマリキー値の独自性を決定するものとして、NULL値は他のNULLを含む全ての他の値と異なると考えられています。

同一のプライマリキー値で2つ以上の行を特徴づけるために、INSERTまたはUPDATEステートメントでテーブル内容の変更を試みた場合は制約違反になります。

SQL標準では、PRIMARY KEYは常にNOT NULLを含める必要があります。

残念ながら、長い期間コーディングを見過ごしていたために、SQLiteには該当しません。

SQLiteでは列がINTEGER PRIMARY KEYでない限り、PRIMARY KEY列内のNULL値を許容します。

我々はSQLiteを標準に準拠するよう変更することができます(将来的に行う可能性があります)が、見出すまでの見過ごされていた期間、SQLiteは広範でしようされており、この問題を修正することで従来のコードが破損することが懸念されます。

そのため現在は、PRIMARY KEY列内でのNULLの許容を続けることを選択しました。

開発者は承知した上で、
将来的にSQLiteがSQL標準に準拠し、それに応じて新たにプログラムを設計する必要があることを、開発者は心得ておく必要があります。

UNIQUE制約は、単一のテーブルがたくさんのUNIQUE制約を持つことを除いて、PRIMARY KEY制約に似ています。

テーブルの各UNIQUE制約は、各行をUNIQUE制約によって識別された列内の値の特有の組み合わせで特徴づける必要があります。

PRIMARY KEY制約と同様に、UNIQUE制約の目的のため、NULL値は(他のNULLを含む)全ての他の値と異なると考えられています。

UNIQUE制約への条件として、列の設定内を同一の値で2つ以上の行を特徴づけるために、INSERTまたはUPDATEステートメントでテーブル内容の変更を試みた場合は制約違反になります。

INTEGER PRIMARY KEY列を別として、UNIQUEとPRIMARY KEY制約は両方とも(『CREATE UNIQUE INDEX』ステートメントを用いた同じ方法で)データベース内にインデックスを生成することで実装されています。

このようなインデックスは、クエリの最適化にデータベース内の他のインデックスと同様に使用されます。

その結果、UNIQUEまたはPRIMARY KEY制約の対象として、既にひとまとめにされている列の設定で、インデックスを生成することの優位性(ただしかなりのオーバーヘッド)は多くありません。

CHECK制約は、列の定義として付随、またはテーブルの制約として指定することができます。

実際には作成に違いはありません。

テーブルに新しい行を挿入または既存の行を更新する度に、式に関連付けされた各CHECK制約の数値を求め、CAST expressionと同様にNUMERIC値にキャストします。

結果がゼロ(整数値0または実数値0.0)の場合、制約違反が発生します。

CHECK式の数値がNULLまたはその他のゼロ以外の値の場合は、制約違反ではありません。

CHECK制約はversion 3.3.0以降からサポートされています。

version 3.3.0より前では、CHECK制約は構文解析が適用されません。

NOT NULL制約は、列の定義として付随させることができますが、テーブルの制約として指定することはできません。

当然のことながら、NOT NULL制約は関連付けされた列がNULL値を含まないことを指示します。

新しい行を挿入する、または既存の制約違反の原因の1つを更新する時に、列の値をNULLに設定しようと試みます。

正確には制約違反がconstraint conflict resolution algorithmによって決定される処理方法を指定します。

各PRIMARY KEY、UNIQUE、NOT NULLそしてCHECK制約は、デフォルトの競合解決アルゴリズムを持ちます。

PRIMARY KEY、UNIQUEそしてNOT NULL制約は、定義内にconflict-clauseを含んでいることによって、デフォルトの競合解決アルゴリズム明示的に割り当てることができます。

また、制約の定義にconflict-clauseを含まない、あるいはCHECK制約の場合、デフォルトの競合解決アルゴリズムはABORTです。

同じテーブル内でも異なる制約は、異なるデフォルトの競合解決アルゴリズムを持っている可能性があります。

追加情報についてはON CONFLICTの項を参照してください。


ROWIDとINTEGER PRIMARY KEY

全てのSQLiteテーブルの全ての行は、テーブル内の行を一意に識別する64ビットの符号付き整数を持ちます。

この整数を通常『rowid』と呼びます。

rowid値は、列名の代わりに『rowid』、『oid』、または『_rowid_』といった特殊なcase-independent名の一つを使用してアクセスすることができます。

テーブルのユーザが定義した列名に『rowid』、『oid』、または『_rowid_』が含まれている場合、その名前は常に明示的に宣言された列を参照しており、整数のrowid値の取得に使用することはできません。

SQLiteの各テーブルのデータは、rowidをキーとして使用し、各テーブルの行のエントリを含むBツリー構造体として格納されます。

これはrowidによるレコードの取得や並べ替えが高速であることを意味します。

指定したrowidのレコード、または指定した範囲内にあるrowidを持つ全てのレコードの検索は、他のPRIMARY KEYまたはインデックス値を指定した類似の検索よりも約2倍高速です。

1つの例外を除いて、テーブルが単一列で構成されているプライマリキーを持ち、その列の宣言された型が大文字と小文字の混合物中の『INTEGER』である場合、その列はrowidのエイリアスとなります。

このような列を通常『整数プライマリキー』と呼びます。

PRIMARY KEY列のみ、宣言された型名が正確に『INTEGER』の場合に整数プライマリキーになります。

その他の『INT』や『BIGINT』、『SHORT INTEGER』、『UNSIGNED INTEGER』のような整数型名は、rowidのエイリアスとしてではなく、プライマリキーの列は整数のaffinityや唯一のインデックスを持つ通常のテーブル列として動作します。

上述での例外は、『PRIMARY KEY DESC』句を含む宣言された型『INTEGER』を持つ列の宣言の場合で、rowidのエイリアスとはならず、整数プライマリキーとして分類されません。

この特異性は設計によるものではありません。

これはSQLiteの初期バージョンのバグが原因です。

バグを修正すると、その結果として非常に深刻な後方互換性の問題が発生する可能性があります。

SQLiteの開発者は、角にあるような馬鹿げたことにこだわって互換性を破壊より、元の動作を保持した方が遥かに良いと感じています。

以下の3つのテーブルの宣言では、列『x』は全てrowidのエイリアス(整数プライマリキー)になります。
  • CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
  • CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
  • CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));
しかし以下の宣言では、『x』はrowidのエイリアスにはなりません。
  • CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
rowid値は他の列値と同様に、UPDATEステートメントを使用して変更する場合があり、組み込みのエイリアス(『rowid』、『oid』または『_rowid_』)か、または整数プライマリキーによって生成されたエイリアスのどちらかを使用することができます。

同様に、INSERTステートメントで挿入された各行のrowidとして使用する値を提供することがあります。

通常のSQLite列とは異なり、整数プライマリキーまたはrowid列は整数値を含める必要があります。

整数プライマリキーやrowid列は、浮動小数点値、文字列、BLOB、またはNULLを保持することはできません。

UPDATEステートメントで、整数プライマリキーやrowid列にNULLまたはblob値、あるいは整数に可逆変換できない文字列または実数を設定しようとした場合、『datatype mismatch』エラーが発生してステートメントは終了します。

INSERTステートメントで、整数に可逆変換できないblob値や文字列、実数を整数プライマリキーまたはrowidの列に挿入しようとした場合、『datatype mismatch』エラーが発生してステートメントは終了します。

INSERTステートメントで、rowidや整数プライマリキーの列にNULL値を挿入しようとした場合、システムは自動的にrowidとして使用する整数値を選択します。

この方法の詳細についてはseparatelyで提供されています。

foreign key constraintparent keyは、rowidを使用することができません。

parent keyは列名のみ使用する必要があります。



参考文献

SQL As Understood By SQLite : CREATE TABLE

0 CommentsPosted in SQLite

« «
REPLACE

» »
SELECT






Bose QuietComfort 20
0 Comments
Leave a comment
管理者にだけ表示を許可する
Top
0 Trackbacks
Top
Calendar
08 | 2017/09 | 10
Sun Mon Tue Wed Thu Fri Sat
- - - - - 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
Recent Articles
iTunes


Swift
Categories
Tips
Profile

水月杏香

Author:水月杏香
永遠の初心者プログラマ。

Wish List
WACOM


ARC
Technical Q&A
情報プロパティリストキー
Start Developing iOS Apps Today
BOSE

Bose QuietComfort 20
Reference
NSApplicationDelegateプロトコル
NSArrayクラス
NSAutoreleasePoolクラス
NSBundleクラス
NSBundle UIKit追加分
NSCalendarクラス
NSCoderクラス
NSCodingプロトコル
NSCopyingプロトコル
NSDataクラス
NSDateクラス
NSDateFormatterクラス
NSDictionaryクラス
NSEntityDescriptionクラス
NSEnumeratorクラス
NSErrorクラス
NSExceptionクラス
NSFetchRequestクラス
NSFileHandleクラス
NSFileManagerクラス
NSIndexPathクラス
NSIndexPath UIKit追加分
NSKeyedArchiverクラス
NSKeyedUnarchiverクラス
NSKeyValueCodingプロトコル
NSLocaleクラス
NSManagedObjectクラス
NSManagedObjectContextクラス
NSManagedObjectModelクラス
NSMutableArrayクラス
NSMutableCopyingプロトコル
NSMutableDictionaryクラス
NSMutableSetクラス
NSNotificationクラス
NSNotificationCenterクラス
NSNullクラス
NSNumberクラス
NSObjectクラス
NSObject UIKit追加分
NSObjectプロトコル
NSPersistentStoreクラス
NSPersistentStoreCoordinatorクラス
NSPredicateクラス
NSPropertyListSerializationクラス
NSRunLoopクラス
NSSetクラス
NSStringクラス
NSString UIKit追加分
NSTimerクラス
NSTimeZoneクラス
NSURLクラス
NSURLProtectionSpaceクラス
NSURLRequestクラス
NSUserDefaultsクラス
NSValueクラス

UIActionSheetクラス
UIActionSheetDelegateプロトコル
UIActivityIndicatorViewクラス
UIAlertViewクラス
UIAlertViewDelegateプロトコル
UIApplicationクラス
UIApplicationDelegateプロトコル
UIBarButtonItemクラス
UIBarItemクラス
UIButtonクラス
UIColorクラス
UIControlクラス
UIDatePickerクラス
UIDeviceクラス
UIEventクラス
UIFontクラス
UIGestureRecognizerクラス
UIImageクラス
UIImageViewクラス
UIKit Function
UILabelクラス
UINavigationControllerクラス
UINavigationItemクラス
UIPickerViewクラス
UIPickerViewDataSourceプロトコル
UIPickerViewDelegateプロトコル
UIPinchGestureRecognizerクラス
UIResponderクラス
UIScreenクラス
UIScrollViewクラス
UISearchBarクラス
UISearchBarDelegateプロトコル
UISegmentedControlクラス
UISliderクラス
UISwipeGestureRecognizerクラス
UISwitchクラス
UITableViewクラス
UITableViewCellクラス
UITableViewControllerクラス
UITableViewDataSourceプロトコル
UITableViewDelegateプロトコル
UITapGestureRecognizerクラス
UITextFieldクラス
UITextFieldDelegateプロトコル
UITextInputTraitsプロトコル
UITextViewクラス
UITextViewDelegateプロトコル
UIToolbarクラス
UITouchクラス
UIViewクラス
UIViewControllerクラス
UIWebViewクラス
UIWebViewDelegateプロトコル
UIWindowクラス

AVAudioPlayerクラス
AVAudioPlayerDelegateプロトコル

CADisplayLinkクラス
CAEAGLLayerクラス
CALayerクラス

CGAffineTransform
CGBitmapContext
CGColor
CGColorSpace
CGContext
CGGeometry
CGImage
CGPath

EAGLContextクラス
EAGLDrawableプロトコル

Foundation Constants
Foundation Data Types
Foundation Functions

MPMediaItemクラス
MPMediaItemArtworkクラス
MPMediaPlaylistクラス
MPMediaPropertyPredicateクラス
MPMediaQueryクラス
MPMusicPlayerControllerクラス

Randomization Services

System Sound Services
Amazon


OpenGL ES
SQLite
Monthly Archives
Recent Comments
Recent TrackBacks
RSS Link
Visitors
QR Code
QR