外部キー制約が弱くなるってなんやって話しですが、分かりやすいように例を交えて
取引先と取引先組織が親子関係、注文には2つの組織(請求先組織, 出荷先組織)を持つとした場合に、
取引先組織を複合主キーのまま定義するとこんな感じ。
create table [取引先] (
[取引先Id] bigint not null primary key,
[名称] nvarchar(100) not null
)
create table [取引先組織] (
[取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
[連番] bigint not null,
[名称] nvarchar(100) not null,
[住所] nvarchar(300) not null
primary key ([取引先Id], [連番])
)
create table [注文] (
[注文Id] bigint not null primary key,
[取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
[請求先組織連番] bigint not null,
[出荷先組織連番] bigint not null,
foreign key ([取引先Id], [請求先組織連番]) references [取引先組織] ([取引先Id], [連番]),
foreign key ([取引先Id], [出荷先組織連番]) references [取引先組織] ([取引先Id], [連番])
)
これを複合主キーをやめて代理キーにするとこんな感じ。
create table [取引先] (
[取引先Id] bigint not null primary key,
[名称] nvarchar(100) not null
)
create table [取引先組織_サロゲートキー] (
[取引先組織Id] bigint not null primary key,
[取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
[名称] nvarchar(100) not null,
[住所] nvarchar(300) not null
)
create table [注文_サロゲートキー] (
[注文Id] bigint not null primary key,
[取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
[請求先組織Id] bigint not null foreign key references [取引先組織_サロゲートキー] ([取引先組織Id]),
[出荷先組織Id] bigint not null foreign key references [取引先組織_サロゲートキー] ([取引先組織Id])
)
一見代理キーの方がスッキリして見えるんだけど、注文の2組織が、「取引先に属している組織」かどうかが DB の制約で担保出来なくなってる。
複合主キーの場合は、外部キー制約の中に 取引先Id を含んでいるので、2組織とも取引先に属している組織って担保出来てる。
複合主キーだと in 句で指定出来ないとかデメリットもあるけど、制約で整合性を担保出来る範囲が広くなるケースもあるのでケースバイケースなんすかねー。
