How to prohibit empty strings globally?

Question

How to prohibit empty strings globally?

vkelman

SSC-Addicted

Points: 406
More actions
July 29, 2009 at 12:35 pm

#208801

We'd like to prohibit empty strings by SQL Server means (not
programmatically on C# level); only NULLs (for nullable fields) should
be allowed.
We can use
ALTER TABLE TableName ADD CONSTRAINT ck_FieldName CHECK ((FieldName <> '') OR (FieldName IS NULL))
But how to do it globally on the level of a database (or SQL Server
instance), so that it wouldn't be necessary to remember to add the
above constraint to each and every table?
(We are using SQL Server 2008.)
Vladimir Kelman
http://pro-thoughts.blogspot.com/

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I don't believe you have any options on this, I think you will need your constraint. Also, I think you really only need the FieldName != '' constraint. The is null is not needed because the DB engine will handle that on its own, it would just be extra overhead..

CEWII

vkelman SSC-Addicted Points: 406 More actions · Answer 2

ty

Vladimir Kelman
http://pro-thoughts.blogspot.com/

myheadhurts Ten Centuries Points: 1262 More actions · Answer 3

...so that it wouldn't be necessary to remember to add the above constraint to each and every table?

Do it in one operation:

EXEC sp_MSForEachTable 'ALTER TABLE ? ADD CONSTRAINT ck_FieldName CHECK (FieldName '')'

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

vkelman SSC-Addicted Points: 406 More actions · Answer 4

Thank you, Mark! It would be nice to have a little SP which goes from filed to filed in each table, checks if a field type is varchar and if it is, applies a non zero length constraint.

I wonder if a significant performance overhead would be introduced.

Vladimir Kelman
http://pro-thoughts.blogspot.com/

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

The more I've thought about this the more I wonder why this is needed, the application or the stored procedures that write the data should be checking this at the time of the insert or update. I too wonder about the write performance costs of this and while I don't think it would be prohibitive, I do think that the more char/varchar columns you have the more it would cost. These are also things that can be handled by the query that pulls the data from the database, but it is still much cleaner to have the process that writes the data be sure..

CEWII

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Elliott W (7/29/2009)
I don't believe you have any options on this, I think you will need your constraint. Also, I think you really only need the FieldName != '' constraint. The is null is not needed because the DB engine will handle that on its own, it would just be extra overhead..
CEWII

Since this is only for nullable columns, a check constraint on ColName '' would fail if the column is null, so you still need to have "or ColName is null"

vkelman SSC-Addicted Points: 406 More actions · Answer 7

You know, in real circumstances, there are many developers. One developer checks for both NULL and zero-length string, when writing a comparison in SQL, another forgets. Although shouldn't be allowed, but in reality some people actually access DB directly to make some quick data fixes. So, it would be nice to guarantee on a database level that there is no zero-length varchars, to eliminate possible comparison errors.

I don't understand what is a need of zero-length string value in a field at all. Wouldn't it be easier if SQL Server only allows NULLs for varchars?

Vladimir Kelman
http://pro-thoughts.blogspot.com/

GSquared SSC Guru Points: 260824 More actions · Answer 8

You might be able to simplify it by creating a user data type, and constraining the data type. Not sure that would solve it, but it might help.

It would be easy enough to build a script that would scan the database for varchar columns and make sure each had a constraint on it, automatically.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

vkelman (8/20/2009)
You know, in real circumstances, there are many developers. One developer checks for both NULL and zero-length string, when writing a comparison in SQL, another forgets. Although shouldn't be allowed, but in reality some people actually access DB directly to make some quick data fixes. So, it would be nice to guarantee on a database level that there is no zero-length varchars, to eliminate possible comparison errors.
I don't understand what is a need of zero-length string value in a field at all. Wouldn't it be easier if SQL Server only allows NULLs for varchars?

I agree with this generally. Code quality is always a factor. There are rare cases where I would write a blank as opposed to NULL, I'm sure I could come up with a business case if I needed to but in general I wouldn't. As far as direct writes, from an app, NO, not allowed, not open for debate, if you need to read or write the DB you do it through a sproc. For quick data fixes the number of people who do it should be fairly small. I gent I used to work stated that we should be coding for the 99% not the 1%. I generally agree with him, we might want to code out as much of that 1% as we can but make sure we don't overburden the engine with things meant to handle something that is not likely to happen or will happen extremely rarely.

Keep in mind I code for things that I don't think will happen but could but if that check is really expensive I might not do it because the payoff is too low..

CEWII