對稱自引用多對多

我正在嘗試對類別的層次結構進行建模，其中一個類別可以有多個父級（本書中描述的重疊樹模型）

我有以下表格

video_categories
 int     id
 string  name

video_category_links
 int     parent_id  (foreign_key to video_categories.id)
 int     child_id   (foreign_key to video_categories.id)

和一個索引

$$ parent_id, child_id $$為了獨特性 The issue with this setup is that A can be parent of B, and B can be parent of A at the same time

Is there a way to specify a db constraint to avoid this or should I ensure it at the application level?

Thank you very much

I would define levels in the hierarchy:

video_categories
 int     id
 int level
 string  name

I would propagate the parent and child levels into your link table:

video_category_links
 int     parent_id  
 int parent_level ((parent_id  , parent_level) foreign_key to video_categories(id, level))
 int     child_id   
 int child_level((child_id  , child_level) foreign_key to video_categories(id, level))
 check(child_level > parent_level)

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