主キーと外部キー SQLite 例を挙げて

以下のセクションでは、 SQLite テーブルを定義し、接続する主キーと外部キーから始まり、各列のデータを検証するNOT NULL、DEFAULT、UNIQUE、およびCHECKルールに至るまで、制約の詳細について説明します。

SQLite 制約

列制約は、列に挿入される値にルールを適用し、データの妥当性を検証します。これらの制約は、テーブル作成時に列定義内で定義されます。制約は、重複、NULL値、関連テーブルに存在しない値など、設定したルールに違反する値を拒否することで、格納データの整合性と正確性を維持します。

SQLite 主キー

主キー列の値はすべて一意でなければならず、NULL値は使用できません。主キーはテーブル内の各行を一意に識別します。

主キーは、単一の列に適用することも、複数の列の組み合わせに適用することもできます。後者の場合、列の値の組み合わせは、テーブルのすべての行で一意である必要があります。

構文：

テーブルに主キーを定義する方法はいくつかあります。

列定義自体では次のようになります。

ColumnName INTEGER NOT NULL PRIMARY KEY;

別の定義として:

PRIMARY KEY(ColumnName);

列の組み合わせを主キーとして作成するには:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite NOT NULL、DEFAULT、UNIQUE、およびCHECK制約

主キーに加えて、 SQLite テーブルに入力された値を検証する複数の列制約を提供します。NOT NULL、DEFAULT、UNIQUE、およびCHECK制約はそれぞれ列定義で定義され、それぞれが列に対して特定のルールを適用します。 データ・タイプ と値。

NOT NULL制約

その SQLite NOT NULL制約は、列にNULL値が含まれることを防止します。

ColumnName INTEGER  NOT NULL;

デフォルトの制約

SQLite デフォルト制約：列に値を入力しなかった場合、代わりにデフォルト値が挿入されます。

具体的な例を挙げますと、以下の通りです。

ColumnName INTEGER DEFAULT 0;

INSERT文を記述する際に、その列に値を指定しない場合、その列の値は0になります。

ユニーク制約

その SQLite UNIQUE制約は、列のすべての値の中で重複する値が存在することを防止します。

具体的な例を挙げますと、以下の通りです。

EmployeeId INTEGER NOT NULL UNIQUE;

これにより、「EmployeeId」の値は一意であることが強制され、重複した値は許可されません。なお、これは「EmployeeId」列の値にのみ適用されます。

チェック制約

その SQLite CHECK制約は、挿入される値をチェックするための条件を設定します。値が条件に一致しない場合、挿入されません。

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

「数量」欄には10未満の値を入力することはできません。

SQLite 外部キー

その SQLite 外部キーとは、あるテーブルに存在する値が、その外部キーが定義されている最初のテーブルと関連のある別のテーブルに存在することを検証する制約のことです。

複数のテーブルを扱う場合、2つのテーブルが共通の列を介して関連付けられていることがあります。一方のテーブルに挿入された値が、もう一方のテーブルの列にも必ず存在するようにしたい場合は、共通の列に外部キー制約を設定する必要があります。

この場合、その列に値を挿入しようとすると、外部キーによって、挿入された値が参照先のテーブルの列に存在することが保証されます。

外部キー制約はデフォルトでは有効になっていないことに注意してください。 SQLiteまず、以下のコマンドを実行して有効にする必要があります。

PRAGMA foreign_keys = ON;

外部キー制約は で導入されました。 SQLite バージョン 3.6.19 以降。

の例 SQLite 外部キー

学生テーブルと学科テーブルという2つのテーブルがあると仮定します。

Studentsテーブルには学生のリストがあり、Departmentsテーブルには学科のリストがあります。各学生はいずれかの学科に所属しており、つまり、各学生にはdepartmentId列があります。

次に、外部キー制約が、Students テーブルの部門 ID の値が Departments テーブルに必ず存在することを保証するためにどのように役立つかを見ていきます。

したがって、StudentsテーブルのDepartmentIdに外部キー制約を作成すると、挿入された各departmentIdはDepartmentsテーブルに存在していなければなりません。

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

外部キー制約が、別のテーブルと関連付けられているテーブルに未定義の要素や値が挿入されるのをどのように防ぐことができるかを確認するために、次の例を見ていきましょう。

この例では、Departments テーブルと Students テーブルの間には外部キー制約が設定されているため、Students テーブルに挿入される departmentId の値は Departments テーブルに既に存在している必要があります。Departments テーブルに存在しない departmentId 値を挿入しようとすると、外部キー制約によって挿入が阻止されます。

部門テーブルに「IT」と「芸術」の2つの部門を以下のように挿入しましょう。 INSERTクエリ:

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

この2つのステートメントを実行すると、Departmentsテーブルに2つの部署が挿入されます。挿入された2つの値が正しく挿入されたかどうかは、その後「SELECT * FROM Departments」クエリを実行することで確認できます。

次に、Departmentsテーブルに存在しないdepartmentIdを持つ新しい学生を挿入してみてください。

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

行は挿入されず、「外部キー制約違反」というエラーが表示されます。

主キーと外部キーの違い SQLite

主キーと外部キーはどちらもデータの整合性を維持するのに役立ちますが、役割は異なります。主キーは単一テーブル内の行を識別するのに対し、外部キーは関連する2つのテーブル間の行をリンクします。以下の表に主な違いをまとめました。

学生と学科の例では、DepartmentIdはDepartmentsテーブルの主キーであり、Studentsテーブルの外部キーでもあります。これにより、各学生が有効な学科に紐付けられます。

SQLite 複合プライマリキー

複合主キーとは、2つ以上の列で構成される主キーのことです。これは、どの列も単独では一意ではないが、列の組み合わせによって各行ごとに一意になる場合に使用されます。 SQLite 結合された値を1つのキーとして扱います。

例えば、履修登録表では、同じ学生が複数のコースに登録したり、同じコースを複数の学生が受講したりすることが認められる場合があるが、学生とコースの組み合わせは一度しか表示されてはならない。

CREATE TABLE Enrollments (
	StudentId INTEGER NOT NULL,
	CourseId INTEGER NOT NULL,
	Grade TEXT,
	PRIMARY KEY (StudentId, CourseId)
);

ここでは、StudentId も CourseId も単独では一意ではありませんが、(StudentId, CourseId) のペアは一意であるため、同じ学生が同じコースに二度登録されることはありません。複合キーを使用する際は、以下の点に注意してください。

• 単一の列では行を一意に識別できない場合は、複合キーを使用してください。
• 複合キーの各列は主キーのルールに従うため、結合された値は一意でなければならず、NULLであってはなりません。
• 複合キーは、単一の列定義内ではなく、テーブルレベルの独立したPRIMARY KEY句として記述されます。

SQLite 外部キーのアクション: ON DELETE および ON UPDATE

外部キーは、参照する親行が削除または更新されたときに、子行に何が起こるかを制御することもできます。これらの参照アクションは、外部キーを定義する際に、ON DELETE句とON UPDATE句によって追加されます。 SQLite 以下の5つのアクションをサポートします。

• 何もしない — デフォルトの動作。子行が親行を参照している場合にエラーが発生します。
• 制限 — 他の変更が実行される前に、削除または更新を即座に防止します。
• NULLを設定 — 子外部キー列をnullに設定します。
• デフォルトに設定 — 子外部キー列を宣言されたデフォルト値に設定します。
• CASCADE — 子行にも同じ変更を適用するため、親行を削除するとその子行も削除されます。

以下の例では、学生テーブルを再作成し、学科を削除すると自動的にその学科の学生も削除され、学科IDを更新すると対応する学生が更新されるようにしています。

CREATE TABLE Students (
	StudentId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	StudentName NVARCHAR(50) NULL,
	DepartmentId INTEGER NOT NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

参照アクションは外部キーのサポートが有効になっている場合にのみ実行されるため、接続の開始時に PRAGMA foreign_keys = ON を実行してください。そうしないと、 SQLite ON DELETE句とON UPDATE句を解析しますが、それらを強制的に適用することはありません。

よくあるご質問