OracleにおけるALTER TABLE文で「ON DELETE CASCADE」を設定する詳細解説

OracleにおけるALTER TABLE文で「ON DELETE CASCADE」を追加する方法

このチュートリアルでは、Oracleデータベースにおいて既存の表に「ON DELETE CASCADE」制約を追加する方法を、ステップバイステップでわかりやすく解説します。

「ON DELETE CASCADE」制約は、親表から参照されているレコードが削除された場合、関連する子表のレコードを自動的に削除する制約です。この制約を設定することで、データ整合性を保ちつつ、データベース操作を簡略化することができます。

前提知識

このチュートリアルを理解するには、以下の知識が必要です。

• Oracleデータベースの基本的な概念
• ALTER TABLE文の使い方
• 外部キー制約の概念

手順

以下の手順に従って、「ON DELETE CASCADE」制約を追加します。

対象となる表と外部キーを確認する

まず、制約を追加する対象となる表と、その表に存在する外部キーを確認する必要があります。外部キーは、別の表を参照する列として定義されます。

SELECT * FROM user_constraints
WHERE constraint_type = 'FK';

このクエリを実行すると、データベース内のすべての外部キー制約が表示されます。制約名、参照している表、参照される列などの情報を確認することができます。

外部キー制約を削除する

既存の外部キー制約を削除してから、新しい制約を追加する必要があります。これは、DROP CONSTRAINT句を使用して行います。

ALTER TABLE child_table
DROP CONSTRAINT constraint_name;

ここで、child_tableは制約を追加する子表の名前、constraint_nameは削除する制約名に置き換えます。

ALTER TABLE文を使用して、新しい外部キー制約を追加します。このとき、ON DELETE CASCADE句を指定することで、カスケード削除を有効にします。

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1, column_name2, ...)
REFERENCES parent_table (column_name1, column_name2, ...)
ON DELETE CASCADE;

ここで、

• child_tableは制約を追加する子表の名前
• constraint_nameは新しい制約名
• column_name1, column_name2, ...は子表の参照列
• parent_tableは参照される親表の名前

に置き換えます。

例

社員情報と所属部署を管理する2つの表があると仮定します。社員情報テーブル (employees) には、社員ID (employee_id)、氏名 (name)、所属部署ID (department_id) の列があります。所属部署テーブル (departments) には、部署ID (department_id)、部署名 (department_name) の列があります。

この場合、以下のコマンドで、employees表にdepartments表を参照する外部キー制約を追加し、カスケード削除を有効にすることができます。

ALTER TABLE employees
ADD CONSTRAINT fk_employees_departments
FOREIGN KEY (department_id)
REFERENCES departments (department_id)
ON DELETE CASCADE;

注意事項

• 「ON DELETE CASCADE」制約を追加する前に、十分なテストを行い、データ整合性の問題が発生しないことを確認してください。
• カスケード削除は、予期せぬデータ損失につながる可能性があるため、注意して使用する必要があります。
• 複雑なリレーションシップを持つデータベースの場合は、データベース管理者に相談することをお勧めします。

例1：社員情報と所属部署

この例では、社員情報テーブル (employees) と所属部署テーブル (departments) を作成し、employees テーブルに departments テーブルを参照する外部キー制約を追加します。また、この制約には ON DELETE CASCADE 句を指定して、カスケード削除を有効にします。

DDL

-- 社員情報テーブルを作成
CREATE TABLE employees (
  employee_id NUMBER(4) PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  department_id NUMBER(4) NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 所属部署テーブルを作成
CREATE TABLE departments (
  department_id NUMBER(4) PRIMARY KEY,
  department_name VARCHAR(50) NOT NULL
);

-- 部署を追加
INSERT INTO departments (department_id, department_name)
VALUES (1, '営業部'),
       (2, '開発部'),
       (3, '人事部');

-- 社員を追加
INSERT INTO employees (employee_id, name, department_id)
VALUES (101, '田中 太郎', 1),
       (102, '佐藤 花子', 2),
       (103, '鈴木 次郎', 3);

-- 部署を削除すると、関連する社員も削除される
DELETE FROM departments
WHERE department_id = 2;

SELECT * FROM employees;

出力

EMPLOYEE_ID NAME        DEPARTMENT_ID
---------- ---------- ------------
101        田中 太郎     1
103        鈴木 次郎     3

例2：注文と注文明細

-- 注文テーブルを作成
CREATE TABLE orders (
  order_id NUMBER(4) PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id NUMBER(4) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 顧客テーブルを作成
CREATE TABLE customers (
  customer_id NUMBER(4) PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL
);

-- 注文明細テーブルを作成
CREATE TABLE order_details (
  order_id NUMBER(4) NOT NULL,
  product_id NUMBER(4) NOT NULL,
  quantity NUMBER(2) NOT NULL,
  unit_price NUMBER(5,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 商品テーブルを作成
CREATE TABLE products (
  product_id NUMBER(4) PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  unit_price NUMBER(5,2) NOT NULL
);

-- 顧客を追加
INSERT INTO customers (customer_id, customer_name)
VALUES (1, '山田 太郎'),
       (2, '鈴木 花子'),
       (3, '佐藤 次郎');

-- 商品を追加
INSERT INTO products (product_id, product_name, unit_price)
VALUES (1, 'ペン', 100),
       (2, 'ノート', 500),
       (3, '消しゴム', 20);

-- 注文を追加
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (101, '2023-12-31', 1),
       (102, '2024-01-01', 2),
       (103, '2024-01-02', 3);

-- 注文明細を追加
INSERT INTO order_details (order_id, product_id, quantity, unit_price)
VALUES (101, 1, 2, 100),
       (101, 2, 1, 500),
       (102, 2, 3, 500),
       (103, 3

Oracleで「ON DELETE CASCADE」制約を追加するその他の方法

詳細

この方法は、チュートリアル「sql, oracle に関連する How to add 'ON DELETE CASCADE' in ALTER TABLE statement のプログラミングについて分かりやすく日本語で解説してください。」で紹介した方法です。

メリット

• シンプルでわかりやすい
• 汎用性が高い

• 外部キー制約を一度削除してから再作成する必要がある

方法2：CREATE TABLE文または ALTER TABLE文の REFERENCES句にON DELETE CASCADEオプションを指定する

この方法は、新しい表を作成する場合や、既存の表の外部キー制約を作成または変更する場合にのみ使用できます。

例：新しい表を作成する場合

CREATE TABLE child_table (
  column_name1 datatype,
  column_name2 datatype,
  ...,
  FOREIGN KEY (column_name1, column_name2, ...)
  REFERENCES parent_table (column_name1, column_name2, ...)
  ON DELETE CASCADE
);

例：既存の表の外部キー制約を作成する場合

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1, column_name2, ...)
REFERENCES parent_table (column_name1, column_name2, ...)
ON DELETE CASCADE;