Updating, Foreign Key Constraints and nulls

Question

I am generating a data template in C#. With the help of the good people on this wonderful site, I've managed to take care of almost every issue. This should be the last problem. Because it's a template I'm working on, I want every field in the table, including nulls. I was helped on how to update nulls by adding (object)this.field ?? DBNull.Value but I have a field that's a foreign key and even though when I look in the database it says null, when I pull the records the value becomes 0. When I try to update this field it says that I am violating foreign key constraints. How can I work around this problem? I thought the null solution would work but it doesn't show as null, it shows as 0.

Thanks

Answer 1

In general it's not a good idea to allow FKs to be null. Some databases (I know oracle does this) enforce this by tying FKs to primary keys rather than columns in other tables. Could you refactor your tables not to need nulls for that column?

Answer 2

Is 0 a possible valid value for the column? If not, just typecast it to null when you encounter one. If it is valid you can still do that, by wrapping it in a conditional that checks the foreign row exists first.