CODEWITHSUNDEEP
sqlsql-server
Mazen Elkashef
Mazen Elkashef

Reputation: 3499

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 used with NVARCHAR columns!

Upvotes: 41

Views: 36306

Answers (6)

tvanfosson
tvanfosson

Reputation: 532595

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''))

Upvotes: 51

dkretz
dkretz

Reputation: 37655

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.

Upvotes: -1

ntziolis
ntziolis

Reputation: 10231

Use a check constraint:

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

Upvotes: 18

Cos Callis
Cos Callis

Reputation: 5084

  • 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...

Upvotes: 2

KeithS
KeithS

Reputation: 71573

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.

Upvotes: 0

Tejs
Tejs

Reputation: 41256

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.

Upvotes: 1

Related Questions