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,
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.
Did you check if levelmanaged can be null?
Regards,
Marcelo
I did not realize that was a possibility.
Thanks.
How do I check?
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!
I was having similar issue. I was trying to force an Identifier created as null (i load from a file) to a value i get from a variable so to join the table from the file loaded with the rest of Aggregates.
I spend quite some time to understand the correct command, so I post the solution here.
I was trying with
update... set... from... where {Deliverable}.[LevelManaged] =0
but it didn't work
i also tried
{Deliverable}.[LevelManaged] = null
like in this solution but no luck. at the end it worked with
{Deliverable}.[LevelManaged] is null