- MS SQL 2008 R2
I have already asked what amounts to one half of the question here, but I am hoping someone in this forum can answer the other half, involving MS SQL server specifically.
So I am doing a code-first ASP.NET MVC 5 project, and there is a business requirement that involves two very specific types of foreign keys to lookup tables. Since these lookup tables must always be loaded with a default value already selected, there is one value in these lookup tables that will always have a
Guid.Empty value (00000000-0000-0000-0000-000000000000) as its primary key, so that it can be easily identified as the default value.
I am trying to find a way to have the foreign keys that make use of these two lookup tables to default to an empty Guid if they are passed a null value. This is added insurance just in case I miss a spot in the code itself where I am supposed to replace a null value with an empty Guid, but don’t. Plus, in this project in particular there will be scheduled weekly imports from an external database, and I want to be able to have all imported data have this default value in its foreign key without having to do extra work. Having the foreign key automatically assume this empty Guid as a default value when passed a null value would be very handy from several different angles.
And yes, I use Guids as
uniqueidentifier GUIDs, not as
nvarchar(128) strings. These foreign keys are non-nullable uniqueidentifier fields.
My other question tried to focus on the MVC Code-First migration itself, but I cannot wait around for an answer in terms of this project (although it will be useful for future projects), so this question on this forum involves the MSSQL server itself. I would like to know how to modify a foreign key
uniqueidentifer field so that it will automatically use a null guid (00000000-0000-0000-0000-000000000000) when passed a null value. I have already tried adding
((00000000-0000-0000-0000-000000000000)) to the
Default Value or Binding property for the foreign key, but both attempts came back with “Error validating the default for column 'column'. Do you want to cancel your changes?”
I am hoping that someone might be able to help me to add a default value to a non-nullable
uniqueidentifier foreign key.