CODEWITHSUNDEEP
mysql
ns15
ns15

Reputation: 8704

Does NOT NULL in mysql accept empty string

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.

Upvotes: 0

Views: 3819

Answers (4)

David
David

Reputation: 218837

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.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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.

Upvotes: 2

sagi
sagi

Reputation: 40481

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.

Upvotes: 0

Charlie Dalsass
Charlie Dalsass

Reputation: 2014

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.

Upvotes: 1

Related Questions