CODEWITHSUNDEEP
sqlsql-serverssms
BILL WAGNER
BILL WAGNER

Reputation: 155

Joining Two Tables with Their Primary Keys

I'm brushing up on my T-SQL using SQL Server Management Studio and have this question that I haven't been able to find the answer to. I am working with the AdventureWorks2017 db and have this INNER JOIN statement that works perfectly:

SELECT 
    FirstName, LastName, AddressLine1 Address, City, 
    StateProvinceCode, State, CountryRegionCode 
FROM 
    Person.Person p
INNER JOIN
    Person.BusinessEntityAddress a ON p.BusinessEntityID = a.BusinessEntityID
INNER JOIN
    Person.Address ON Address.AddressID a = a.AddressID
INNER JOIN
    Person.StateProvince s ON s.StateProvinceID = a.StateProvinceID
WHERE 
    CountryRegionCode = 'US'

But here's the deal: p.BusinessEntityID and a.BusinessEntityID are not related by a foreign key, even though both BusinessEntityID columns are identical, but neither is a foreign key to the other. These two tables have a junction table between them called BusinessEntity.

Obviously, there is something here I don't understand. There is probably a simple explanation that I don't know. If someone can enlighten me, it would be much appreciated.

Upvotes: 0

Views: 51

Answers (1)

B.Porter
B.Porter

Reputation: 367

The purpose of a Foreign Key is to enforce referential integrity of data. It enforces that you can't enter a row into the child table if the specified key doesn't exist in the parent table.

In this case there is no integrity to enforce between the Person and BusinessEntityAddress table and so no Foreign Key is supplied. The integrity is between the BusinessEntity and BusinessEntityAddress - i.e you can't have a BusinessEntityAddress if the BusinessEntity doesn't exist.

Upvotes: 2

Related Questions