Preventing both Nulls and Zero-Length-Strings 1

[MD5150]

How do I enforce a required entry rule from a backend table in SQL Server 2000?

I normally like the description field in my reference tables to be a required entry field that doesn't allow both nulls or zero-length-strings.

In Access, this is easily done via a "Required" property setting. And I know that the "Allow Nulls" property in SQL Server is suppose to be the equivelent, but it doesn't really function the same because it still allows for zero-length-strings. Apparently Access converts its zero-length-strings to Nulls before saving, which I do find useful.

I have my reasons for wanting this, so please hold back the lengthly debates regarding the uses of nulls vs. zero-length-strings and when a developer might want to store a zero-length-string. I've read all of the articles and understand the issues, but I still want to duplicate the "required" behavior of Access using SQL Server.

I know that I need either a trigger or a constraint and have read that a constraint is the preferred method. So I've tried:

([FieldName] <> "")

...but this doesn't seem to work.

I'm just starting my official move to SQL Server, so if someone could give me a syntax example of either a trigger or contraint that would prevent a zero-length-string, that would be a big help.

Greatly appreciated, thanks.

Mike

 

[r937]

create table test_constraint
( id integer not null primary key identity
, foo varchar(9) not null
, constraint some_foo check ( len(foo) > 0 )
)

r937.com | rudy.ca

 

[MD5150]

Excellent! It works perfectly. Thanks for the help, Rudy.

Mike