SQL Server 外部キー非主キーリレーションシップデータベース

2024-04-10

SQL Serverにおける外部キーと非主キーの関係

SQL Serverでは、外部キーを使用して、異なるテーブル間の関連性を定義することができます。外部キーは、子テーブルの列を、親テーブルの列に関連付けるものです。通常、外部キーは親テーブルの主キーと関連付けられますが、非主キーと関連付けることも可能です。

非主キーと外部キー

非主キーとは、テーブル内の一意性を保証しない列です。一方、外部キーは、子テーブルの列を親テーブルの列に関連付けるためのものです。外部キーは、親テーブルの主キーまたは一意キーと関連付けることができます。

非主キーと外部キーを使用する利点

データの整合性を保つことができます。

親テーブル：Customers

CustomerID (主キー)
CustomerName

子テーブル：Orders

OrderDate

この例では、OrdersテーブルのCustomerID列は、CustomersテーブルのCustomerID列に関連付けられています。つまり、Ordersテーブルに存在するCustomerIDは、Customersテーブルに存在するCustomerIDと一致する必要があります。

外部キー列は、親テーブルの列と同じデータ型である必要があります。
外部キー列は、NULL値を許可できない場合があります。
親テーブルの列が変更された場合は、子テーブルの外部キーも更新する必要があります。

-- テーブルの作成

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    CustomerName varchar(50)
);

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate datetime
);

-- データの挿入

INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'John Doe'),
       (2, 'Jane Doe');

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (1, 1, '2023-01-01'),
       (2, 2, '2023-02-01');

-- データの参照

SELECT *
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

このコードを実行すると、以下の結果が表示されます。

OrderID CustomerID OrderDate
------- -------- --------
1       1       2023-01-01
2       2       2023-02-01

非主キーと外部キーを関連付ける他の方法

一意キーとの関連付け

外部キーは、親テーブルの一意キーと関連付けることもできます。一意キーとは、テーブル内の一意性を保証する列です。

例

-- テーブルの作成

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    CustomerName varchar(50)
);

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate datetime
);

-- データの挿入

INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'John Doe'),
       (2, 'Jane Doe');

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (1, 1, '2023-01-01'),
       (2, 2, '2023-02-01');

-- データの参照

SELECT *
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

外部キー制約を作成する際に、ON UPDATEおよびON DELETEオプションを使用して、親テーブルのレコードが更新または削除されたときの動作を指定することができます。

オプション

ON UPDATE CASCADE：親テーブルのレコードが更新された場合、子テーブルの関連するレコードも自動的に更新されます。

-- テーブルの作成

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    CustomerName varchar(50)
);

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    OrderDate datetime
);

この例では、OrdersテーブルのCustomerID列は、CustomersテーブルのCustomerID列に関連付けられています。ON UPDATE CASCADEオプションが指定されているため、Customersテーブルのレコードが更新された場合、Ordersテーブルの関連するレコードも自動的に更新されます。

CHECK 制約を使用して、外部キー列の値が親テーブルの列の値に存在することを確認することができます。

-- テーブルの作成

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    CustomerName varchar(50)
);

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int,
    OrderDate datetime,
    CHECK (CustomerID IN (SELECT CustomerID FROM Customers))
);