How to constraint no empty strings on an NVARCHAR column

I add a NOT NULL constraint on my NVARCHAR column so it couldn't allow any empty values. but unfortunately SQL Server deals with NULL and empty values as two separate values!

So how do I make the server throw an exception when inserting nothing to the column?

I'm thinking using the constraint CHECK but I didn't find any samples when u开发者_开发知识库sed with NVARCHAR columns!

You could add a check constraint that ensures that the string isn't empty.

CREATE TABLE [dbo].[Foo](
    [bar] [nvarchar](50) NOT NULL
)

ALTER TABLE [dbo].[Foo] WITH CHECK 
ADD  CONSTRAINT [CK_Foo] CHECK  (([bar]<>N''))

Use a check constraint:

CREATE TABLE SomeTable( 
    SomeColumn VARCHAR(50) NOT NULL CHECK (SomeColumn <> '')
)

• If the value is being assigned a string property you can add logic at the property set method.
• If you would rather enforce this at the SQL layer then tvanfosson's answer is good.
• Unfortunately using a trigger is not going to work because a trigger can not affect the record being inserted...

This sounds like a job for a trigger. Basically, you'd want a trigger on insert such that it checks the new value, and if it's empty string, you cause the trigger to fail.

How about a check constraint that the column value doesn't equal an empty string? A bit cleaner than a trigger, and it can be modeled in code using many ORMs including NHibernate, so your app layer can be told to expect it.

Failing that, when assigning the field you never want to be empty up in your application layer, try using a NullIfBlank() extension method:

public static string NullIfBlank(this string input)
{
   return String.IsNullOrWhitespace(input) ? null : input;
}

Null strings, empty strings, and strings that only contain spaces, newlines, tabspaces, etc will all come back as null. Now, this will require you to make sure you aren't doing any simple concatenations with this field that you don't also want to come back null: null + "any string" == null.

It would be nice if MSSQL provided a clean way to configure empty strings to map to NULL, when that's the meaning in context. But the database is not the best place to be trapping this since there's no error handling without raising an error to another abstraction level where it should have been handled in the first place.