外键约束 - 如何删除引用的记录?

我偶然发现了一个非常基本的问题，因此没有真正考虑清楚.

我有两张桌子，比方说:

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.