Do Foreign Key constraints get checked on an SQL update statement that doesn't update the columns with the Constraint?

Do Foreign Key constraints开发者_StackOverflow中文版 get checked on an SQL update statement that doesn't update the columns with the Constraint? (In 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?)

---

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

---

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.

---

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:

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.