I set a MySQL column to "NOT NULL" but still I can insert an empty value

Question

In MySQL I have a table with Column1 as NOT NULL:

create table myTable 
(
    Column1 int not null,
    Column2 int not null
)

I can still insert an empty value like this:

INSERT INTO `myTable` ( `Column1` ,  `Column2` )
VALUES ( '66', '' );

How can I make the MySQL column also disallow blankstring?

Answer 1

EMPTY STRINGS

In ORACLE an empty string is used to represent NULL. In virtually everything else, however, an empty string is still a string, and so not NULL.

INTS

In your case you're actually inserting STRINGS into an INT column. This forces an implicit CAST operation.

When your RDBMS is converting the string '' to an INT it must get the value 0. As 0 is not NULL, this gets inserted.

A more valid test would be:

INSERT INTO `plekz`.`countries` (`Column1 ` , `Column2`)
VALUES (66, NULL);

EDIT

Sorry, I only half read your question. You also ask how to stop '' being inserted.

Your first problem is that you're inserting STRINGS and the table is defined as having INT fields. You can put constraints on the data that gets inserted, but these constraints will apply the the value after an conversion to an INT. Unless you want to prevent the value 0 from also being inserted, there is nothing you can do to the table to prevent this scenario.

Your better bet is to address why you are inserting strings in the first place. You could use a stored procedure that takes, and checks, the strings before converting them to INTs and then inserting them. Or, better still, you could make the checks in your client application.

A technically available option is to make the fields CHAR fields, then put a constraint on the fields, preventing '' from being inserted. I would strongly recommend against this.