Does NOT NULL in mysql accept empty string

Question

I have two tables. One with int as primary key and other with a varchar primary key.

When an empty string is inserted ( "" ) i get programming error for int but successfully stores for varchar. How does this work?

Is there a way to make varchar fields not accept empty strings? I want them to give some database error which i can catch.

Yes. varchar means Variable-length character data. — Phylogenesis
– Phylogenesis, Commented Nov 23, 2016 at 13:31
Is there a way to make varchar fields not accept empty strings? — ns15
– ns15, Commented Nov 23, 2016 at 13:36
That is a different question entirely, but typically you validate fields using triggers. — Phylogenesis
– Phylogenesis, Commented Nov 23, 2016 at 13:40
@shadow0359: Looks like there's already an answer for that: stackoverflow.com/questions/2514178/… — David
– David, Commented Nov 23, 2016 at 13:44

David · Accepted Answer · 2016-11-23 13:41:59Z

4

When a empty string is inserted ( "" ) i get programming error for int

Because a string is not an int. And there's no way to automatically convert a non-numeric string to an int.

but successfully stores for varchar

Because a string is a varchar. Columns like CHAR and VARCHAR are for storing character data, which is what a string is.

None of these values are NULL. NULL is the absence of a value. An empty string is still a value.

edited Nov 23, 2016 at 13:41

answered Nov 23, 2016 at 13:28

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-11-23 13:28:28Z

2

An empty string is not NULL, in almost every database and by the ANSI definition.

It so happens that Oracle treats empty strings as NULL values, which is generally rather confusing. But that is an exception to the SQL language.

answered Nov 23, 2016 at 13:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Charlie Dalsass · Accepted Answer · 2016-11-23 13:27:50Z

1

An empty string is different from null. (This is true in MySQL and in many programming languages as well.) Try inserting null into the primary key for the varchar. It should fail on the insert - just like the int.

As @David says above, the empty string cannot be converted to an int, and so MySQL chooses null instead.

answered Nov 23, 2016 at 13:27

Charlie Dalsass

2,01419 silver badges25 bronze badges

Comments

sagi · Accepted Answer · 2016-11-23 13:28:24Z

0

Not sure what you are trying to do, and we need to see the code to know what is the best way to fix this problem, but you can try this:

CASE WHEN IN_IntValue = '' THEN NULL ELSE IN_IntValue

In general, only Oracle as far as I know look at empty string as NULL. In MySQL they are two different things.

answered Nov 23, 2016 at 13:28

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Collectives™ on Stack Overflow

Does NOT NULL in mysql accept empty string

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related