Advanced SQL Update Set Identifier = nullidentifier()

I am writing an advanced SQL to update all records in a table, changing an entity identifier field to null. This is the query I wrote. It generates an error when I test, stating the update statement conflicted with the foreign key constraint.

Update {Deliverable} set {Deliverable}.[LevelManaged] = @param

LevelManaged is a static entity. I am passing a nullidentifier() to @param to test. When I pass one of the valid values, the test passes.

How do you properly set a foreign key of type <entity> Identifier to null?





Hi Bill,

Did you check if levelmanaged can be null?

Regards,

Marcelo

I did not realize that was a possibility. 

Thanks. 

How do I check? 

Solution

Hi Bill,

Funny, I just fixed a bug that was caused by the same issue. The thing is, that to set a column to NULL in SQL, you need the syntax "columnName = NULL". However, since you specify "columnName = @columnValue", in case of a NullIdentifier(), the value is actually "0". So it tries to set the value to 0, and that's why the constraint kicks in. So the correct statement would be:

UPDATE {Deliverable}
SET {Deliverable}.[LevelManaged] = CASE WHEN @param = 0 THEN NULL ELSE @param END

Of course, like Marcelo mentioned, LevelManaged must not be mandatory.

Solution

Thanks for that explanation and syntax. I look forward to trying it. 

To all: no need to respond to my'mandatory' question. My mind was locked on screen treatments instead of database treatments. 

Works like a charm!

Good to hear :) Happy coding!