Can I put a constraint on a sql server table to not allow empty string?

Question

I'm getting this error Linq to Sql Error - "String must be exactly one character long"

Is it possible to put a constraint on a nullable varchar(1) field to allow null but not allow an empty string?

Answer 1

Yes -- just use CHECK constraints:

ALTER TABLE myTable
    ADD CONSTRAINT CK_table_colHasLength CHECK (col1 IS NULL OR col1 <> '')

Answer 2

If you define an empty string as one that contains nothing but spaces then you might want to consider using the following constraint.

ALTER TABLE myTable ADD CONSTRAINT CK_table_colHasLength CHECK
(col1 is null or len(col1) != 0)

Note that the len() function will return 0 if the string contains only spaces - regardless of how many it contains.

Answer 3

That is not a DB-side problem. The Linq-to-SQL designer in VS2008 has a mapping bug where varchar(1) and nvarchar(1) incorrectly gets mapped to System.Char instead of System.String. System.Char does not allow an empty string while an empty string are perfectly valid values for varchar(1) and nvarchar(1).

As a workaround you can change the mapping in the L2S designer from System.Char to System.String for the affected fields.

See:
http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/e8082fc1-a5be-47bf-9b06-cd1bde19bea0
...and...
http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/ac91e587-6e91-454c-9fa2-bab20b7a258c