SQLで既存テーブルに新列を追加する方法：外部キー制約付き・無し

2024-04-23

SQLで既存テーブルに外部キー制約付きの新列を追加する方法

方法1：ALTER TABLE文を2回実行する

新しい列を追加する
外部キー制約を定義する

-- 例：テーブル `orders` に `customer_id` 列を追加し、`customers` テーブルの `id` 列を参照する外部キー制約を定義する

-- 1. 新しい列を追加する
ALTER TABLE orders
ADD customer_id INT;

-- 2. 外部キー制約を定義する
ALTER TABLE orders
ADD CONSTRAINT FK_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id);

方法2：ALTER TABLE文を1回実行する

一部のデータベースでは、ALTER TABLE文を1回実行して、新しい列を追加し、同時に外部キー制約を定義することができます。

-- 例：テーブル `orders` に `customer_id` 列を追加し、`customers` テーブルの `id` 列を参照する外部キー制約を定義する

ALTER TABLE orders
ADD customer_id INT
REFERENCES customers(id);

注意事項

外部キー制約を定義するには、参照される側のテーブルに主キーが定義されている必要があります。
外部キー制約を定義すると、参照される側のテーブルのデータが変更された場合、参照側のテーブルのデータも自動的に更新されます。
外部キー制約を定義すると、参照側のテーブルに参照される側のテーブルに存在しない値が挿入されるのを防ぎます。

補足

上記の方法は、基本的な外部キー制約の定義方法です。オプションで、ON DELETE CASCADEやON UPDATE CASCADEなどの句を追加して、参照される側のテーブルのデータが削除または更新された場合に参照側のテーブルのデータがどのように処理されるかを指定することができます。

また、一部のデータベースでは、生成された列名を使用せずに、外部キー制約に名前を付けることができます。

例

-- 例：テーブル `orders` に `customer_id` 列を追加し、`customers` テーブルの `id` 列を参照する外部キー制約を定義し、制約名を `fk_orders_customers` とする

ALTER TABLE orders
ADD customer_id INT
CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id);

これらのオプションの詳細については、各データベースのマニュアルを参照してください。

方法1：ALTER TABLE文を2回実行する

-- 1. 新しい列を追加する
ALTER TABLE orders
ADD customer_id INT;

-- 2. 外部キー制約を定義する
ALTER TABLE orders
ADD CONSTRAINT FK_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id);

-- 新しい列を追加し、同時に外部キー制約を定義する
ALTER TABLE orders
ADD customer_id INT
REFERENCES customers(id);

説明

上記のコードは、以下の操作を実行します。

orders テーブルに customer_id という名前の新しい列を追加します。この列は、INT 型のデータを持ちます。
customer_id 列に外部キー制約を定義します。この制約により、customer_id 列の値は、customers テーブルの id 列の値と一致する必要があります。
外部キー制約に FK_orders_customers という名前を付けます。これはオプションです。

このコードは、MySQL、PostgreSQL、Microsoft SQL Serverなどの主要なデータベースで動作します。
実際のコードは、使用するデータベースやテーブルのスキーマに合わせて調整する必要があります。

以下の例は、customers テーブルと orders テーブルの構造とデータを示しています。

customers` テーブル

列名	データ型	説明
id	INT	主キー
name	VARCHAR(255)	顧客名

orders` テーブル

列名	データ型	説明
order_id	INT	主キー
customer_id	INT	顧客ID
product_id	INT	商品ID
order_date	DATE	注文日

上記の例では、orders テーブルの customer_id 列は、customers テーブルの id 列を参照する外部キー制約となります。つまり、orders テーブルの customer_id 列の値は、customers テーブルの id 列の値が存在する必要があります。

この制約により、orders テーブルに存在する注文は、必ず customers テーブルに存在する顧客に関連付けられるようになります。

このサンプルコードはあくまでも一例であり、実際の状況に合わせて調整する必要があります。

SQLで既存テーブルに外部キー制約付きの新列を追加するその他の方法

方法3：CREATE TABLE ... SELECT文を使用する

この方法は、既存のテーブルからデータを抽出して、新しいテーブルを作成し、同時に外部キー制約を定義するものです。

-- 例：`orders` テーブルから `customer_id` 列と `order_date` 列を抽出し、`customers` テーブルの `id` 列を参照する外部キー制約を定義して、`new_orders` テーブルを作成する

CREATE TABLE new_orders (
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
)
SELECT customer_id, order_date
FROM orders;

方法4：既存の列を変更する

既存の列のデータ型を変更して、外部キー制約を満たすようにすることもできます。ただし、この方法は、列のデータ型が大きく異なる場合や、既存のデータに互換性の無い変更が必要となる場合などに適しています。

-- 例：`orders` テーブルの `customer_number` 列のデータ型を `INT` に変更し、`customers` テーブルの `id` 列を参照する外部キー制約を定義する

ALTER TABLE orders
MODIFY customer_number INT;

ALTER TABLE orders
ADD CONSTRAINT FK_orders_customers
FOREIGN KEY (customer_number)
REFERENCES customers(id);