問(wèn)題描述
我偶然發(fā)現(xiàn)了一個(gè)非常基本的問(wèn)題，因此沒(méi)有真正考慮清楚.
我有兩張桌子，比方說(shuō):
Table1: col1 PK,表 2:col1 FK 參考表 1(col1)當(dāng)我想從 Table1 中刪除記錄時(shí)，該記錄被 Table2 中的記錄引用，SQL 會(huì)拋出錯(cuò)誤，該 DELETE 語(yǔ)句沖突有約束.這是可以理解和期望的行為.
如果我真的想刪除該行并在所有引用被刪除鍵的行中將 Table2 中的引用設(shè)置為 NULL 怎么辦?
我知道我可以手動(dòng)完成 - 將 Table2 中的引用設(shè)置為 NULL 然后刪除.但是有沒(méi)有更簡(jiǎn)潔的方法?
您可以通過(guò)在 FK 關(guān)系上設(shè)置 Set Null 選項(xiàng)來(lái)配置插入和更新規(guī)范規(guī)則.我附上了一張來(lái)自 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.
這篇關(guān)于外鍵約束 - 如何刪除引用的記錄?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！