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