来自多个相关表的复合外键 [英] Composite Foreign Key from multiple related tables

问题描述

刚开始学习SQL并有一个我不知道的问题.

Just beginning to learn about SQL and had a question I couldn't figure out.

我有一个基于以下表及其主键的设置，表之间具有相同名称的列受外键约束:

I have a setup based on the following tables and their primary keys, the columns with the same name between tables are constrained by foreign keys:

公司:

• CompanyId

部门:

• CompanyId
• DivisionId

资源:

• CompanyId
• ResourceId

DivisionResource:

DivisionResource :

• CompanyId
• DivisionId
• ResourceId

• DivisionResource用于在部门和资源之间创建多对多关系并对其进行约束，以便部门只能链接到同一公司的资源.
• 没有DivisionResource，Division和Resource不需要CompanyId作为包含唯一记录的主键.

所以我的问题是:是否有一种方法可以创建与DivisionResource创建的约束类似的约束，而不会迫使Division和Resource在其主键中包含额外的列?

So my question is this: Is there a way to create a similar constraint as DivisionResource creates without forcing Division and Resource to have an extra column in its primary key?

推荐答案

ResourceCompany和DivisionCompany是连接表.他们的主键中将包含CompanyId，但是Resource和Division将具有一列的主键.这就是您想要的.

ResourceCompany and DivisionCompany in the schema below are connecting tables. They will have CompanyId in their primary key but Resource and Division will have primary keys with one column. This is what you looked for.

资源-> ResourceCompany

Resource -> ResourceCompany

DivisionResource-> ResourceCompany

DivisionResource -> ResourceCompany

部门->部门公司

DivisionResource-> DivisionCompany

DivisionResource -> DivisionCompany

create table Company (CompanyId int primary key);

create table DivisionCompany (
    CompanyId int foreign key references Company(CompanyId), 
    DivisionId int, 
    constraint pk_div_company primary key (DivisionId, CompanyId)
    );

create table Division (
    DivisionId int primary key,
    CompanyId int,
    constraint fk_div_company foreign key (DivisionId, CompanyId) references DivisionCompany(DivisionId, CompanyId));

create table ResourceCompany (
    CompanyId int foreign key references Company(CompanyId), 
    ResourceId int, 
    constraint pk_res primary key (ResourceId, CompanyId));

create table Resource(
    ResourceId int primary key,
    CompanyId int, 
    constraint fk_res_company foreign key (ResourceId, CompanyId) references ResourceCompany(ResourceId, CompanyId)
    );

create table DivisionResource(
    CompanyId int,
    DivisionId int, 
    ResourceId int,
    constraint pk_DivRes primary key (DivisionId, ResourceId),
    constraint fk_DivCompany foreign key (DivisionId, CompanyId) references DivisionCompany(DivisionId, CompanyId),
    constraint fk_ResCompany foreign key (ResourceId, CompanyId) references ResourceCompany(ResourceId, CompanyId)
    );

这篇关于来自多个相关表的复合外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！