5

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.

  • 3
    SQL Server disregards trailing spaces, which is why a single space would still violate the constraint. – JNK Dec 21 '11 at 15:31
8

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)
  )  
  • +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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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