SQLite 制約管理 (SQLite Constraint Management)

はじめに

この実験 (lab) では、データ整合性を確保するために、SQLite の制約管理について学びます。まず、外部キー制約を定義してテーブル間の関係を確立し、CHECK 制約を実装し、複合キーを作成します。最後に、制約違反をテストして、SQLite がこれらのルールをどのように適用するかを理解します。この実践的な経験を通して、SQLite データベース内でデータの整合性を維持する方法を実践的に理解することができます。

外部キー制約を持つテーブルの作成

このステップでは、customers テーブルと orders テーブルの 2 つのテーブルを作成し、それらの間に外部キー制約を確立します。この制約により、各注文が有効な顧客に関連付けられるようになります。

まず、ターミナルで次のコマンドを実行して、SQLite シェルを開きます。

sqlite3 /home/labex/project/database.db

このコマンドは、database.db という名前の SQLite データベースファイルへの接続を開きます。ファイルが存在しない場合、SQLite はファイルを作成します。sqlite> プロンプトが表示されるはずです。

次に、次の SQL コマンドを使用して customers テーブルを作成します。

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

このコマンドは、customers という名前のテーブルを、customer_id、first_name、last_name、および email の 4 つの列で作成します。customer_id 列は主キーであり、新しい顧客ごとに自動的にインクリメントされます。

次に、customers テーブルを参照する外部キー制約を使用して、orders テーブルを作成します。

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

このコマンドは、orders という名前のテーブルを、order_id、customer_id、order_date、および total の 4 つの列で作成します。order_id 列は主キーです。FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 句は外部キー制約を確立し、orders テーブルの customer_id が customers テーブルの有効な customer_id を参照するようにします。

テーブルが作成されたことを確認するには、次のコマンドを使用します。

.tables

このコマンドは、データベース内のすべてのテーブルを一覧表示します。出力に customers と orders の両方が表示されるはずです。

データの挿入と外部キー制約のテスト

このステップでは、customers テーブルと orders テーブルにデータを挿入し、外部キー制約をテストします。

まず、SQLite で外部キー制約を有効にする必要があります (デフォルトでは無効になっています)。

PRAGMA foreign_keys = ON;

次に、customers テーブルにデータを挿入します。

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');

このコマンドは、2 人の新しい顧客を customers テーブルに挿入します。customer_id は自動的に割り当てられます。

次に、既存の顧客の 1 人を参照して、orders テーブルに注文を挿入します。

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

このコマンドは、新しい注文を orders テーブルに挿入し、customer_id が 1 の顧客に関連付けます。

次に、customers テーブルに存在しない customer_id を持つ注文を挿入してみます。

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

次のようなエラーメッセージが表示されます：Error: FOREIGN KEY constraint failed。これは、外部キー制約が正しく機能しており、存在しない顧客の注文を作成できないことを確認します。

テーブル内のデータを表示するには、次のコマンドを使用します。

SELECT * FROM customers;
SELECT * FROM orders;

これらのコマンドは、それぞれ customers テーブルと orders テーブルの内容を表示します。

CHECK 制約の実装

このステップでは、すべてのメールアドレスに @ 記号が含まれていることを保証する CHECK 制約を含む、新しい customers_with_check テーブルを作成します。

まず、CHECK 制約を使用して新しいテーブルを直接作成しましょう。

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

このコマンドは、customers_with_check という名前の新しいテーブルを、email 列に @ 記号が含まれていることを保証する CHECK 制約付きで作成します。LIKE 演算子はパターンマッチングに使用され、% は任意の文字シーケンスに一致するワイルドカード文字です。

次に、既存の顧客データを新しいテーブルにコピーしましょう。

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

次に、無効なメールアドレスを持つ新しい顧客を挿入してみます。

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

次のようなエラーメッセージが表示されるはずです：Error: CHECK constraint failed: email。これは、CHECK 制約が無効なメールアドレスの挿入を防いだことを示しています。

CHECK 制約が機能していることを確認するには、有効なメールアドレスを持つ顧客を挿入します。

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');

このコマンドは、メールアドレスに @ 記号が含まれているため、正常に実行されるはずです。

複合キーを持つテーブルの作成

このステップでは、student_id 列と course_id 列で構成される複合キーを持つ enrollments という名前のテーブルを作成します。

SQLite シェルで次の SQL ステートメントを実行します。

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

このコマンドは、student_id、course_id、および enrollment_date の 3 つの列を持つ enrollments という名前のテーブルを作成します。PRIMARY KEY (student_id, course_id) 句は、テーブルの主キーが student_id 列と course_id 列の両方で構成されることを指定します。これは、student_id と course_id の組み合わせが、テーブル内の各行で一意である必要があることを意味します。

次に、enrollments テーブルにデータを挿入します。

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

これにより、3 つの行が enrollments テーブルに挿入されます。

次に、既存の行と同じ student_id と course_id を持つ行を挿入してみます。

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

次のようなエラーメッセージが表示されるはずです：Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id。これは、複合キー制約が重複行の挿入を防いだことを示しています。

テーブルが正しく作成されたことを確認するには、SQLite シェルで .tables コマンドを使用できます。

.tables

出力に enrollments が表示されるはずです。

まとめ

この実験 (lab) では、データの整合性を確保するために、SQLite で制約を定義および実装する方法を学びました。外部キー制約を持つテーブルを作成し、データを検証するための CHECK 制約を実装し、複数の列に基づいて行を一意に識別するための複合キーを持つテーブルを作成しました。これらの制約を理解し活用することで、データの整合性を維持する堅牢で信頼性の高いデータベースを構築できます。