【完全ガイド】SQL Server、Oracle、PostgreSQLにおける外部キー設定
2024-04-02
外部キーが NULL および/または重複する場合
外部キーとは？
外部キーは、複数のテーブル間でデータの関連性を定義するものです。親テーブルの主キー列を参照し、子テーブルの列に格納されます。
NULL 値と重複許可
外部キーは、以下の 2 つの観点から設定できます。
- NULL 値の許可: 子テーブルの列に
NULL値を格納できるかどうか。
各データベースにおける動作
| データベース | NULL 値の許可 | 重複許可 | デフォルト設定 |
|---|---|---|---|
| SQL Server | 許可 | 許可 | NULL 値と重複を許可 |
| Oracle | 許可 | 許可 | NULL 値と重複を許可 |
| PostgreSQL | 制限あり | 制限あり | NULL 値と重複を許可しない |
詳細解説
-
- 子テーブルのレコードが親テーブルのレコードに関連付けられていない場合に
NULL値を使用できます。 - 外部キー列に
NULL値が格納されている場合、子テーブルのレコードは親テーブルのレコードと関連付けられていないことを意味します。
- 子テーブルのレコードが親テーブルのレコードに関連付けられていない場合に
-
重複許可
- 子テーブルの複数のレコードが同じ親テーブルのレコードを参照できる場合に有効です。
- 1 対多の関係性において、子テーブルのレコードが複数の親テーブルのレコードに関連付けられる場合に有効です。
各設定の利点と欠点
-
- 利点: データの挿入と更新が容易
- 欠点: データ整合性の問題が発生する可能性
-
- 利点: データ整合性を保ちやすい
実用的な例
設定時の注意点
- データベースの要件に基づいて適切な設定を選択する必要があります。
- 設定によってデータ整合性やデータ操作の複雑さに影響が出ることがあります。
CREATE TABLE dbo.Customers (
CustomerID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
OrdersID int NULL,
FOREIGN KEY (OrdersID) REFERENCES dbo.Orders (OrderID)
);
CREATE TABLE dbo.Orders (
OrderID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
OrderDate datetime NOT NULL,
CustomerID int NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES dbo.Customers (CustomerID)
);
Oracle
CREATE TABLE customers (
customer_id number(10) NOT NULL,
first_name varchar2(50) NOT NULL,
last_name varchar2(50) NOT NULL,
orders_id number(10) NULL,
FOREIGN KEY (orders_id) REFERENCES orders (order_id)
);
CREATE TABLE orders (
order_id number(10) NOT NULL,
order_date date NOT NULL,
customer_id number(10) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
PostgreSQL
CREATE TABLE customers (
customer_id serial PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
orders_id integer NULL,
FOREIGN KEY (orders_id) REFERENCES orders (order_id)
);
CREATE TABLE orders (
order_id serial PRIMARY KEY,
order_date date NOT NULL,
customer_id integer NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
- 2 つのテーブル (
CustomersとOrders) を作成します。 CustomersテーブルのOrdersID列はOrdersテーブルのOrderID列を外部キーとして参照します。- 外部キーの
NULL値と重複許可は、各データベースの設定に従います。
サンプルコードの利用方法
- 上記のサンプルコードをそれぞれのデータベース環境で実行することで、外部キーを設定したテーブルを作成できます。
- サンプルコードを参考に、実際のデータモデルに合わせてテーブルを作成してください。
- 外部キーの設定は、データの整合性を保つために重要な役割を果たします。
- 各データベースにおける外部キーの動作の違いを理解し、適切な設定を選択する必要があります。
外部キーの NULL 値と重複を制御するその他の方法
CHECK 制約を使用して、外部キー列の値に制限を設けることができます。
例:
ALTER TABLE Customers
ADD CONSTRAINT chk_OrdersID CHECK (OrdersID IS NULL OR EXISTS (SELECT 1 FROM Orders WHERE OrderID = Customers.OrdersID));
この制約は、Customers テーブルの OrdersID 列が NULL 値であるか、Orders テーブルに存在する値であることを保証します。
DEFAULT 制約を使用して、外部キー列のデフォルト値を設定できます。
ALTER TABLE Customers
ADD CONSTRAINT def_OrdersID DEFAULT (NULL) FOR OrdersID;
この制約は、Customers テーブルの OrdersID 列に値が設定されていない場合、NULL 値を挿入します。
UNIQUE 制約を使用して、外部キー列に重複を許可しないようにできます。
ALTER TABLE Customers
ADD CONSTRAINT uq_OrdersID UNIQUE (OrdersID);
トリガーを使用して、外部キー列の値が変更された際に処理を実行できます。
CREATE TRIGGER trg_Customers_OrdersID
ON Customers
AFTER UPDATE
AS
BEGIN
IF (NEW.OrdersID IS NOT NULL)
BEGIN
IF NOT EXISTS (SELECT 1 FROM Orders WHERE OrderID = NEW.OrdersID)
BEGIN
RAISE ERROR 'OrdersID does not exist.';
END;
END;
END;
- シンプルな方法で外部キーの NULL 値と重複を制御したい場合は、
CHECK制約またはDEFAULT制約を使用するのがおすすめです。 - より複雑な要件を満たしたい場合は、
UNIQUE制約またはトリガーを使用する必要があります。
sql sql-server oracle
