互斥的一對多關係表如何定義查詢

你好我是以下情況。

我有 5 張桌子

Entity
------------  
idEntity
entityName
...
...
idCountry_fkey
idRegion_fkey
idAggregate_fkey

Country
---------------
idCountry
countryName

Region
---------------
idCountry_fkey
idRegion
RegionName

Aggregate
---------------
idAggregate
AggregateName

Aggregate_to_Country
----------------------
idAC
idCountry_fkey
idAggregate_fkey

我使用以下約束對實體表強制執行互斥性

   CHECK (
   (("idAggregate" IS NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NOT NULL)) 
   OR 
   (("idAggregate" IS NULL) AND ("idRegion" IS NOT NULL) AND ("idCountry" IS NULL)) 
   OR 
   (("idAggregate" IS NOT NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NULL))
);

我有以下問題：

1. 這是處理這種情況的正確方法嗎
2. 我如何查詢才能擁有：

index | entityname | Aggregate | Country | Region | Value
----------------------------------------------------------
case1 |  A         |  Eurozone |  null   | null   | 1
----------------------------------------------------------
case2  |  B        |    LATAM  |  Brazil | null   | 22
----------------------------------------------------------
case3  |  C        |  Europe   |  Italy  | Lazio | 44

1. 案例1）當實體與聚合相關時
2. 案例 2) 當 Entity 與 Country 相關，且 Country 在 Aggregate Case 時
3. Case 3) Entity與Region相關，Region與Country相關，Country在Aggregate中

它會是這樣的：

SELECT e.index,
      e.entityname,
      COALESCE(a1.AggregateName, a2.AggregateName, a3.AggregateName) AggregateName,
      COALESCE(c2.CountryName, c3.CountryName) CountryName, 
      r3.Region,
      e.Value
FROM Entity e
-- when idAggregate_fkey is specified
LEFT JOIN Aggregate a1 ON a1.idAggregate = e.idAggregate_fkey
-- when idCountry_fkey is specified
LEFT JOIN Country c2 ON c2.idCountry = e.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac2 ON ac2.idCountry_fkey = e.idCountry_fkey
LEFT JOIN Aggregate a2 ON a2.idAggregate = ac2.idAggregate_fkey
-- when idRegion_fkey is specified
LEFT JOIN Region r3 ON r3.idRegion = e.idRegion_fkey
LEFT JOIN Country c3 ON c3.idCountry = r3.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac3 ON ac3.idCountry_fkey = c3.idCountry_fkey
LEFT JOIN Aggregate a3 ON a3.idAggregate = ac3.idAggregate_fkey

1. 檢查每個分支的加入順序和條件 - 我可能會犯錯誤。
2. 檢查某些連接是否可能是 INNER。

引用自：https://dba.stackexchange.com/questions/239842