T-SQL技术问询：使用主键连接无外键关联的AdventureWorks2017表

Hey there! Let's unpack your questions about that INNER JOIN scenario in AdventureWorks2017—great catch on noticing the missing foreign key and the intermediate junction table. Here's a breakdown of what's going on:

1. 为什么没有外键，INNER JOIN依然能正常工作？ #

First off, SQL JOIN operations don't require foreign key constraints to run—they're two totally separate things:

• Foreign keys are a data integrity tool: they enforce that values in a child table's column must exist in the parent table's matching column, preventing invalid or orphaned records.
• An INNER JOIN is purely a query logic tool: it only cares that the values in the joined columns (your p.BusinessEntityID and a.BusinessEntityID) match up. As long as there are overlapping values, the join will return rows where those values align.

For example, even if there was no logical relationship between the two tables, you could still write a valid join like this (though it might not make business sense):

SELECT p.FirstName, a.JobTitle
FROM Person.Person p
INNER JOIN HumanResources.Employee a
  ON p.BusinessEntityID = a.BusinessEntityID;

In AdventureWorks specifically, these two tables have a one-to-one business relationship (each person record maps to one employee record, and vice versa), so joining directly on BusinessEntityID is logically correct—even without a formal foreign key constraint.

2. 那中间的"BusinessE..."连接表有什么用？ #

那个中间表（我猜你说的是Person.BusinessEntityContact）是用来处理表之间的多对多关系的。举个例子：

• 一个BusinessEntity（比如客户或供应商）可能对应多个联系人，而一个联系人也可能关联多个业务实体，这个连接表就用来存储这些配对关系。

但这和你直接连接Person.Person与HumanResources.Employee并不冲突——这两张表本身是直接的一对一业务关联，所以不需要通过连接表中转，属于数据库 schema 里不同的关联场景。

3. 要不要在这里补建外键约束？ #

如果这个一对一关系是数据模型里的固定逻辑，建议补建外键约束：

• 它能阻止无效的BusinessEntityID值插入到任意一张表中（比如出现没有对应人员记录的员工数据），保持数据一致性。
• 它还能帮助SQL Server生成更优的查询计划，提升JOIN语句及相关查询的执行效率。

内容的提问来源于stack exchange，提问作者BILL WAGNER