Microsoft SQL Server
外部キー

外部キーの関係/制約

外部キーを使用すると、2つのテーブル間の関係を定義できます。 1つの（親）テーブルには、テーブル内の行を一意に識別する主キーが必要です。他の（子）表は、列の1つの親からの主キーの値を持つことができます。 FOREIGN KEY REFERENCES制約を使用すると、子表の値が親表の主キー値として存在する必要があります。

この例では、CompanyIdプライマリキーを持つ親Companyテーブルと、この従業員が働いている会社のIDを持つChild Employeeテーブルがあります。

create table Company (
   CompanyId int primary key,
   Name nvarchar(200)
)
create table Employee (
    EmployeeId int,
    Name nvarchar(200),
    CompanyId int
        foreign key references Company(companyId)
)

外部キー参照によって、Employee.CompanyId列に挿入された値もCompany.CompanyId列に存在する必要があります。また、子テーブルに一致するcompanyIdを持つ従業員が少なくとも1人いる場合、誰も社内テーブル内の会社を削除することはできません。

FOREIGN KEY関係では、2つの表の行を「リンク解除」できないようにします。

親子行間の関係の維持

companyId 1のCompanyテーブルに1つの行があるとしましょう。companyId 1のemployeeテーブルに行を挿入できます。

insert into Employee values (17, 'John', 1)

ただし、CompanyIdが存在しない従業員を挿入することはできません。

insert into Employee values (17, 'John', 111111)

メッセージ547、レベル16、状態0、行12 INSERTステートメントは、FOREIGN KEY制約 "FK__Employee__Compan__1EE485AA"と競合しました。競合は、データベース "MyDb"、テーブル "dbo.Company"、列 "CompanyId"で発生しました。ステートメントは終了されました。

また、それを参照する従業員表に少なくとも1つの子行がある限り、会社表内の親行を削除することはできません。

delete from company where CompanyId = 1

メッセージ547、レベル16、状態0、行14 DELETEステートメントは、REFERENCE制約 "FK__Employee__Compan__1EE485AA"と競合しました。競合は、データベース "MyDb"、テーブル "dbo.Employee"、列 "CompanyId"で発生しました。ステートメントは終了されました。

外部キーの関係により、会社と従業員の行は「リンク解除」されません。

既存のテーブルに外部キー関係を追加する

依然として関係にない既存の表にFOREIGN KEY制約を追加できます。 Employee table CompanyIdの列が、外部キーの関係を持たないCompanyとEmployeeの表があるとします。 ALTER TABLE文を使用すると、その表の他の表および主キーを参照する既存の列に外部キー制約を追加できます。

alter table Employee
    add  foreign key (CompanyId) references Company(CompanyId)

既存のテーブルに外部キーを追加する

constraintを持つFOREIGN KEY列は、まだ関係にない既存の表に追加できます。 EmployeeテーブルにCompanyIdカラムがないCompanyテーブルとEmployeeテーブルがあるとします。 ALTER TABLE文を使用すると、その表の他の表と主キーを参照する外部キー制約付きの新しい列を追加できます。

alter table Employee
    add CompanyId int foreign key references Company(CompanyId)

外部キー制約に関する情報の取得

sys.foreignkeysシステムビューは、データベース内のすべての外部キー関係に関する情報を返します。

select name,
 OBJECT_NAME(referenced_object_id) as [parent table],
 OBJECT_NAME(parent_object_id) as [child table],
 delete_referential_action_desc,
 update_referential_action_desc
from sys.foreign_keys