Do Foreign Key constraints get checked on an SQL update statement that doesn't update the columns with the Constraint? (In MS SQL Server)
是否在不使用约束更新列的SQL更新语句上检查外键约束? (在MS SQL Server中)
Say I have a couple of tables with the following columns:
假设我有几个包含以下列的表:
OrderItems
- OrderItemID
- OrderItemTypeID (FK to a OrderItemTypeID column on another table called OrderItemTypes)
- ItemName
If I just update
如果我只是更新
update [dbo].[OrderItems]
set [ItemName] = 'Product 3'
where [OrderItemID] = 2508
Will the FK constraint do it's lookup/check with the update statement above? (even thought the update is not change the value of that column?)
FK约束是否会使用上面的update语句进行查找/检查? (甚至认为更新不会改变该列的值?)
3 个解决方案
#1
8
No, the foreign key is not checked. This is pretty easy to see by examining the execution plans of two different updates.
不,不检查外键。通过检查两个不同更新的执行计划,很容易看出这一点。
create table a (
id int primary key
)
create table b (
id int,
fkid int
)
alter table b add foreign key (fkid) references a(id)
insert into a values (1)
insert into a values (2)
insert into b values (5,1) -- Seek on table a's PK
update b set id = 6 where id = 5 -- No seek on table a's PK
update b set fkid = 2 where id = 6 -- Seek on table a's PK
drop table b
drop table a
#2
3
No. Since the SQL update isn't updating a column containing a constraint, what exactly would SQL Server be checking in this case? This is similar to asking, "does an insert trigger get fired if I only do an update?" Answer is no.
不可以。由于SQL更新没有更新包含约束的列,在这种情况下SQL Server究竟会检查什么?这类似于询问,“如果我只进行更新会导致插入触发器被触发吗?”答案是否定的。
#3
1
There is a case when the FK not existing will prevent updates to other columns even though the FK is not changed and that is when the FK is created WITH NOCHECK and thus not checked at the time of creation. Per Books Online:
有一种情况是FK不存在会阻止对其他列的更新,即使FK未更改,也就是FK创建时没有检查,因此在创建时不会检查。每本在线书籍:
If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.
如果您不想针对现有数据验证新的CHECK或FOREIGN KEY约束,请使用WITH NOCHECK。除极少数情况外,我们不建议这样做。将在所有后续数据更新中评估新约束。添加约束时由WITH NOCHECK抑制的任何约束违规可能会导致将来更新失败,如果他们使用不符合约束的数据更新行。
#1
8
No, the foreign key is not checked. This is pretty easy to see by examining the execution plans of two different updates.
不,不检查外键。通过检查两个不同更新的执行计划,很容易看出这一点。
create table a (
id int primary key
)
create table b (
id int,
fkid int
)
alter table b add foreign key (fkid) references a(id)
insert into a values (1)
insert into a values (2)
insert into b values (5,1) -- Seek on table a's PK
update b set id = 6 where id = 5 -- No seek on table a's PK
update b set fkid = 2 where id = 6 -- Seek on table a's PK
drop table b
drop table a
#2
3
No. Since the SQL update isn't updating a column containing a constraint, what exactly would SQL Server be checking in this case? This is similar to asking, "does an insert trigger get fired if I only do an update?" Answer is no.
不可以。由于SQL更新没有更新包含约束的列,在这种情况下SQL Server究竟会检查什么?这类似于询问,“如果我只进行更新会导致插入触发器被触发吗?”答案是否定的。
#3
1
There is a case when the FK not existing will prevent updates to other columns even though the FK is not changed and that is when the FK is created WITH NOCHECK and thus not checked at the time of creation. Per Books Online:
有一种情况是FK不存在会阻止对其他列的更新,即使FK未更改,也就是FK创建时没有检查,因此在创建时不会检查。每本在线书籍:
If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.
如果您不想针对现有数据验证新的CHECK或FOREIGN KEY约束,请使用WITH NOCHECK。除极少数情况外,我们不建议这样做。将在所有后续数据更新中评估新约束。添加约束时由WITH NOCHECK抑制的任何约束违规可能会导致将来更新失败,如果他们使用不符合约束的数据更新行。