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.

up vote 1 down vote accepted

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 ;
  • Isn't the whole point of a default value such that an insert with a null value on a field that is set to not null will not raise an error but use the default value instead? – René Kåbis Jul 21 '16 at 22:13
  • Also, what is the convention between single brackets (0), double brackets ((0)) and single quotation marks '0' where the default value is concerned? – René Kåbis Jul 21 '16 at 22:14
  • The point of the default value is to provide a value when an INSERT has values for some of the columns of the table (the rest get their respective defaults). If the INSERT has a null fro a column, the server assumes that you know what you are doing and want to insert a null value. – ypercubeᵀᴹ Jul 21 '16 at 22:20
  • brackets (single or double) have no meaning. You can write (((0)-5)) The result will be the number -5. For certain types, like text (CHAR, VARCHAR), datetimes (DATE, DATETIME, TIME) and UNIQUEIDENTIFIER, single quotes must be used for literals. – ypercubeᵀᴹ Jul 21 '16 at 22:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.