Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

On SQL Server not nullable varchar columns can hold empty strings.

Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL.

If you design a database schema suitable for both RDBMS it seems to be a good idea to add to each not nullable varchar column on SQL Server a constraint which disallows empty strings.

But which is the best way to formulate such a constraint?

I started with

if OBJECT_ID('varchar_without_empty_cols') > 0 drop table varchar_without_empty_cols
go

create table varchar_without_empty_cols (
id int ,
val varchar(10) not null CHECK (val <> '') 
)
go


insert into varchar_without_empty_cols values (1, ' ')  -- this ought be OK
go
insert into varchar_without_empty_cols values (2, '')   -- this has to violate the check
go
insert into varchar_without_empty_cols values (3, null) -- this violates the not null
go 

select * from varchar_without_empty_cols

But this constraint not only inhibits empty strings, which is intended, but it also inhibits strings consisting of a single character, and that is not what I intend.

share|improve this question
2  
SQL Server disregards trailing spaces, which is why a single space would still violate the constraint. –  JNK Dec 21 '11 at 15:31
add comment

1 Answer

up vote 8 down vote accepted

To allow strings containing only spaces but disallow empty strings you can use

CREATE TABLE varchar_without_empty_cols
  (
     id  INT,
     val VARCHAR(10) NOT NULL CHECK (DATALENGTH(val) > 0)
  )  
share|improve this answer
    
+1 But I'm curious if there other ways to write this constraint. –  bernd_k Dec 21 '11 at 15:37
2  
@bernd_k - CHECK (val LIKE '_%' ) –  Martin Smith Dec 21 '11 at 15:39
    
It seems that both Oracle and SQL-Server need special treatment to identify empty strings. –  ypercube Dec 21 '11 at 15:48
    
@Martin: I suppose that CHECK (val > '' ) would not work either in SQL-Server. –  ypercube Dec 21 '11 at 15:56
1  
@ypercube CHECK (val > '' ) doesn't work in SQL-Server. –  bernd_k Dec 21 '11 at 16:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.