問題描述
我偶然發現了一個非常基本的問題，因此沒有真正考慮清楚.
我有兩張桌子，比方說:
Table1: col1 PK,表 2:col1 FK 參考表 1(col1)當我想從 Table1 中刪除記錄時，該記錄被 Table2 中的記錄引用，SQL 會拋出錯誤，該 DELETE 語句沖突有約束.這是可以理解和期望的行為.
如果我真的想刪除該行并在所有引用被刪除鍵的行中將 Table2 中的引用設置為 NULL 怎么辦?
我知道我可以手動完成 - 將 Table2 中的引用設置為 NULL 然后刪除.但是有沒有更簡潔的方法?
您可以通過在 FK 關系上設置 Set Null 選項來配置插入和更新規范規則.我附上了一張來自 Sql Server Management Studio 的圖片.此外，還有一篇有用的文章
I stumbled across a problem, which is very basic, thus didn't really think it through.
I have two tables, let's say:
Table1: col1 PK,
Table2: col1 FK references Table1(col1)
When I want to delete record from Table1, which is referenced by record in Table2, SQL would throw error, that DELETE statement coflicted with a constraint. Which is understandable and desired behaviour.
What if I really want to delete the row and set reference in Table2 to NULL in all rows referencing key being deleted?
I know that I can do it manually - set references in Table2 to NULL and then delete. But is there more concise way?
You could configure Insert and Update Specification rule by setting Set Null option on FK relationship. I have attached an image from Sql Server Management Studio. Also, there is a useful article here.
這篇關于外鍵約束 - 如何刪除引用的記錄?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！