SQL Server 根据两个表中的组合键自动删除 FK 记录？

Question

我想要一个表来存储关于数据库中任何数据表中任何记录的注释，并在删除相关数据记录时删除注释记录。

我可以在note表、table-id表和数据表之间建立组合FK键关系并使用ON DELETE CASCADE还是我需要在数据表上使用ON DELETE触发器，或者什么是最好的方法？

例如有三张 table ，FRUITS，BUILDINGS，CLOTHES；

FRUITS_ID   NAME    |     BUILDINGS_ID  TEXT     |    CLOTHES_ID    TYPE
-------------------------------------------------------------------------
1           APPLE   |     1             HOUSE    |    1             COAT
2           LEMON   |     2             HOSPITAL |    2             BOOTS
3           PLUM    |     3             PRISON   |          

我定义了一个表id表；

REFTABLE_ID     TABLENAME
-------------------------                   
21              FRUITS                      
22              BUILDINGS                       
23              CLOTHES                     

notes 表使用该表中的表 ID 和记录 ID；

NOTES_ID    REFTABLE_ID  REFTABLE_RECORD_ID  NOTETEXT       
-------------------------------------------------------------   
1           21           1                   Make a pie             
2           21           2                   Try lemonade               
3           22           2                   Handles emergencies                
4           23           1                   Can be waterproof              

因此 BUILDINGS (REFTABLE_ID 22) 中记录“HOSPITAL”(BUILDINGS_ID 2) 的注释文本是“Handles emergencies”(NOTES_ID 3)

当 BUILDINGS 记录 id 2 被删除时，“自动”删除注释记录 id 3 的最佳方法是什么？

谢谢!

Answer 1

如果您使用的是 SQL Server 2008 或更高版本，我认为您可以使用 MERGE 语句。此外，如果您向所有三个表 FRUITS、BUILDINGS 和 CLOTHES 添加额外的列“TABLE_ID”，那么我认为您可以像下面那样进行操作(假设带有表 ID 的表的名称是“TABLE_IDS”):

MERGE NOTES AS Target
USING (SELECT TBL.REFTABLE_ID, B.BUILDINGS_ID, F.FRUITS_ID, C.CLOTHES_ID 
FROM 
BUILDINGS [B] JOIN TABLE_IDS [TBL] ON B.TABLE_ID=TBL.TABLE_ID JOIN FRUITS [F] 
ON F.TABLE_ID=TBL.TABLE_ID JOIN CLOTHES [C] ON C.TABLE_ID=TBL.TABLE_ID) 
As Source
ON ((Target.REFTABLE_RECORD_ID = Source.BUILDINGS_ID  AND Target.REFTABLE_ID 
= Source.REFTABLE_ID) 
OR (Target.REFTABLE_RECORD_ID =Source.FRUITS_ID AND Target.REFTABLE_ID = 
Source.REFTABLE_ID) 
OR (Target.REFTABLE_RECORD_ID =Source.CLOTHES_ID AND Target.REFTABLE_ID = 
Source.REFTABLE_ID))
WHEN MATCHED 
THEN DELETE
OUTPUT Deleted.*;