【PostgreSQL】 SQL構文（DML)：DELETE文

2024.10.03

PostgreSQL SQL構文（DML)：DELETE文

DELETE 文は、テーブルのレコードを削除するSQL文です。


DELETEの基本構文

  • DELETE FROM：レコード削除テーブルの指定。
  • テーブル名：削除対象のテーブル名。
  • WHERE 条件: 削除対象となるレコードを特定する条件。これを指定しないとテーブルの全レコードが削除されます。
DELETE FROM テーブル名 WHERE 条件;


DELETE文の例
usersテーブルからuser_id = 123のレコードを削除します。

DELETE FROM users WHERE user_id = 123;


USING句

複数のテーブルを基に削除するレコードを絞り込むことができます。下のSQLはordersテーブルから、日本にいる顧客の注文を削除します。

DELETE FROM orders 
USING customers 
WHERE orders.customer_id = customers.customer_id AND customers.country = 'Japan';


RETURNING句

削除された行の情報を返すことができます。これは削除後にそのレコードの情報を確認したい場合に便利です。下のSQL文は、レコード削除後に、username, email を返しています。

DELETE FROM users WHERE user_id = 123 RETURNING username, email;


RETURNING username, email の取得方法「Pythonの場合」

DELETE文を実行した後、cur.fetchone()メソッドで取得します。

import psycopg2

# データベースに接続
conn = psycopg2.connect(
    host="localhost",    # ホスト名
    database="your_db",  # データベース名
    user="your_user",    # ユーザー名
    password="your_password"  # パスワード
)

# カーソルを作成
cur = conn.cursor()

# DELETE文を実行し、RETURNINGで削除した行のusernameとemailを取得
try:
    delete_query = """
    DELETE FROM users
    WHERE user_id = %s
    RETURNING username, email;
    """
    
    # 削除したいユーザーのIDを指定
    user_id = 123
    
    # クエリを実行
    cur.execute(delete_query, (user_id,))
    
    # 削除された行の情報を取得
    deleted_user = cur.fetchone()
    
    if deleted_user:
        username, email = deleted_user
        print(f"Deleted User: Username={username}, Email={email}")
    else:
        print(f"No user found with user_id={user_id}")

    # 変更をコミット
    conn.commit()

except Exception as e:
    print(f"An error occurred: {e}")
    # エラーが発生した場合、ロールバックする
    conn.rollback()

finally:
    # カーソルと接続を閉じる
    cur.close()
    conn.close()


  • データベース接続：psycopg2.connect() でデータベースに接続します。

  • カーソル作成：conn.cursor() でデータベース操作を行うカーソルを作成します。

  • クエリ作成：DELETE文に RETURNING username, email;を含めます。これにより、削除されたレコードの 「username」 と、「email」を返します。

  • クエリ実行：cur.execute(query) でクエリを実行します。

  • usernameとemailの取得：rows = cur.fetchall()でクエリの実行結果を取得し、「username, email = deleted_user」で取得します。

  • コミット：conn.commit() でトランザクションを確定します。

  • ロールバック：SQL文の実行でエラーが発生した場合は、conn.rollback()でレコードを元に戻します。

  • 接続終了：最後にカーソルとデータベース接続を閉じます。


外部キー制約と各オプション

外部キー制約とは、あるテーブルのカラムが別のテーブルの主キーを参照する制約です。これは、データベースにおけるリレーション（関係）を定義し、データの整合性を保つために使用されます。


例えば、次のような2つのテーブルがあるとします。

  1. usersテーブル（親テーブル）:
    • user_id（主キー）
    • その他のユーザー情報
  2. ordersテーブル（子テーブル）:
    • order_id（主キー）
    • user_id（外部キーとしてusersテーブルのuser_idを参照）
    • 注文情報

この場合、ordersテーブルのuser_idはusersテーブルのuser_idを参照しています。つまり、ordersテーブルの各行は、どのユーザーが注文したのかを示すためにusersテーブルのユーザーに依存しています。この依存関係を「外部キー制約」と呼びます。

外部キー制約が設定されている場合、親テーブル（usersテーブル）の行が削除されると、子テーブル（ordersテーブル）にそのユーザーに関連する注文が存在するかどうかを確認する必要があります。このとき、依存する行がどのように処理されるかは、外部キー制約で設定されるオプションによって異なります。

CASCADE句

userテーブルのuser_idを、外部キーとして使用している子テーブルのレコードも一緒に削除します。

DELETE FROM users WHERE user_id = 123 CASCADE;


SET NULL 句

外部キーで関連付けられた親テーブルの行が削除されたとき、子テーブルの外部キーの値をNULLに設定します。SET NULLオプションは子テーブル作成時に定義します。
下のSQL文は、子テーブルorders作成時にusersテーブルのuser_idを外部キーとして参照しています。usersテーブルの行が削除されると、ordersテーブルの該当するuser_idの値はNULLに設定されます。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE SET NULL,
    order_date DATE
);


RESTRICT 句

親テーブルのレコードが削除されようとしたときに、子テーブルに依存するレコードが存在する場合、親テーブルのレコード削除を禁止するオプションです。これも子テーブル作成時に定義します。
下の例では、usersテーブルに関連するordersテーブルのレコードがある場合、usersテーブルのレコードは削除できません。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE RESTRICT,
    order_date DATE
);


NO ACTION 句（デフォルト）

RESTRICT オプションと同様に、親テーブルのレコードが削除されようとしたときに、子テーブルに、依存するレコードが存在する場合、親テーブルのレコード削除を禁止するオプションです。これも子テーブル作成時に定義します。
RESTRICT オプションは、親テーブルのレコードが削除されようとしたときに適用されますが、NO ACTION オプションは、トランザクションの最後に適用されます。
下の例では、usersテーブルの行が削除されようとしても、ordersテーブルに依存する行がある場合、usersテーブルのレコード削除は無効となります。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE NO ACTION,
    order_date DATE
);



DELETE文のまとめ

  • レコードを削除するSQL文である。

  • USING句：複数のテーブルを基に削除するレコードを絞り込むことができる。

  • RETURNING句：削除されたレコードの値を取得する事ができる。

  • CASCADE句：外部キーとして使用している子テーブルのレコードも一緒に削除する。

  • SET NULL句：外部キーの値をNULLに設定する

  • RESTRICT句：外部キーとして参照しているレコードがある場合、削除を禁止する。

  • NO ACTION句：RESTRICTと動作は同様だが、トランザクションの最後に削除を無効とする。

タイトルとURLをコピーしました