Configuring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign table

Question

Project Details:

• 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) and ((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.

Answer 1

To add a default is easy:

CREATE TABLE dbo.t
( tid INT PRIMARY KEY,
  ggid UNIQUEIDENTIFIER NOT NULL
      DEFAULT '00000000-0000-0000-0000-000000000000'
 ) ;

Then the following insert will work:

INSERT INTO dbo.t
  (tid)
VALUES
  (1), (2) ;

Test at rextester.

If you want NULLs to not raise errors but be converted to the default, I think the easiest way is by using an INSTEAD insert trigger on the table. Something like this:

CREATE TRIGGER t_replace_null_guid_with_default
ON dbo.t
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.t (tid, ggid)
    SELECT tid, COALESCE(ggid, '00000000-0000-0000-0000-000000000000') 
    FROM inserted;
END ;