對非主鍵的列的外鍵引用

我知道，外鍵應該唯一標識行，因此引用主鍵。

但是，請考慮我的範例：有 4 個預定義類別，對於這些類別的某些值組合，存在模組層次結構。層次結構由父模組列實現。

我的方法是在包含這些列的單個表中實現它：

• 第 1 類（例如，地區）
• 第 2 類（例如，學校類型）
• 第 3 類（例如，類）
• 第 4 類（例如，主題）
• 模組標題
• 父模組標題
• 和一個由所有類別+模組標題組成的主鍵。

我希望父模組標題引用模組標題列。現在因為外鍵需要引用整個主鍵，我需要插入列，如父類別 1、父類別 2、父類別 3、父類別 4。但父模組始終與子模組具有相同的值所有 4 個類別，因此這些列將是多餘的。

如果我可以在我的表聲明中寫這樣的東西，那將是理想的：

— 虛構的 SQL！—

create table modules (
 ...,
 module_title varchar(50),
 foreign key parent_module_title varchar(50) 
   references modules(module_title) where 
     modules.category1 = category1 and modules.category2 = category2 and 
     modules.category3 = category3 and modules.category4 = category4
 )

或類似的東西：

— 虛構的 SQL！—

 ...
 parent_module_title varchar(50)
   check (exists 
   (select * from modules where modules.module_title = parent_module_title))
 ...

這些都是虛構的 wishfulSQL（後者 throws cannot use subquery in check constraint），但是有沒有辦法實現我的目標？如果這很重要，我正在使用 Postgres。

首先是關於層次結構的說明。您目前嘗試用於模組的技術稱為鄰接列表。我鼓勵您查找其他一些更合適的 SQL 技術，例如：路徑列舉、嵌套集、閉包表。

但是，這句話似乎有問題：

但是父模組在所有 4 個類別中始終具有與子模組相同的值，因此這些列將是多餘的。

這聽起來不尋常，層次結構可能如下所示：

1        region/
1.1      region/school/
1.1.1    region/school/class/
1.1.1.1  region/school/class/subject/

我會冒險並聲稱這裡的問題不是關於外鍵，而是關於在 SQL 中實現層次結構。

為了簡化問題，我建議為您的項目嘗試路徑列舉，並在需要時探索其他技術。

-- Module titled TITLE is categorized as CAT_PATH.
--
module {TITLE, CAT_PATH}
   PK {TITLE}

其中CAT_PATH之一在哪裡：

• region/
• region/school/
• region/school/class/
• region/school/class/subject/

這可能會導致一系列不同的問題和問題，但這些問題將更容易解決。

您的問題是您的數據庫設計根本沒有標準化。將數據拆分到多個表中，例如region、等subject，module以便表中的每一行描述這些實體之一。

然後很容易在實體之間建立外鍵關係，從而保證參照完整性。

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