SQLiteでデータのバリデーションと整合性を確保する最良の手法

# NOT NULL制約を適用したテーブル作成
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

# UNIQUE制約を適用したテーブル作成
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE
);

# Pythonでのデータ挿入と例外処理
try:
  cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", (name, email))
except sqlite3.IntegrityError:
  print("重複したemailです。")

# Pythonでのトランザクションの使用例
conn = sqlite3.connect("example.db")
cursor = conn.cursor()

try:
  cursor.execute("BEGIN")
  cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@email.com"))
  cursor.execute("INSERT INTO orders (user_id, amount) VALUES (?, ?)", (cursor.lastrowid, 1000))
  cursor.execute("COMMIT")
except sqlite3.Error:
  cursor.execute("ROLLBACK")
  print("トランザクション失敗、ロールバックします。")

# 価格がマイナスにならないようにするトリガー
CREATE TRIGGER prevent_negative_price
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  SELECT CASE
    WHEN NEW.price < 0 THEN RAISE(ABORT, "価格がマイナスです")
  END;
END;

# 外部キー制約の設定
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  FOREIGN KEY(user_id) REFERENCES users(id)
);