I set the MySQL column to "NOT NULL", but still 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 force a MySQL column to also ban an empty row?

Answer 1

EMPTY LINE

In ORACLE, an empty string is used to represent NULL. However, in almost everything else, an empty string is still a string, and therefore not NULL.

INTS

In your case, you are actually inserting STRINGS into the INT column. This results in an implicit CAST operation.

When your RDBMS converts the string '' to INT, it should get the value 0. Since 0 is not NULL, this is inserted.

A more correct test would be:

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

EDIT

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

Your first problem is that you insert STRINGS, and the table is defined as having INT fields. You can set restrictions on the data to be inserted, but these restrictions will apply the value after converting to INT. If you do not want the value 0 to be inserted as well, you cannot do anything for the table to prevent this scenario.

Your best bet is to decide why you insert rows first. You can use a stored procedure that accepts and validates strings before converting them to INT, and then inserting them. Or, even better, you can do checks in your client application.

The technically available option is to create CHAR fields and then set a limit on the fields, preventing them from being added. '' I would highly recommend .

Answer 2

You are inserting an empty string, not NULL . The restriction is limited only by NULL values, and it will appear that your database does not enter empty strings into NULL when it converts them to INT (which raises the additional question of why you insert string literals into INT columns ...)

Answer 3

As Martin mentions, it depends on your RDBMS. Oracle treats empty strings as NULL, while others do not. See This SO Post .

Answer 4

MySQL, how to ban an empty string:

• Create a table:

   mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec) 

• Create a pre-insert trigger to check for an empty string and deny.

   mysql> create trigger foo before insert on yar -> for each row -> begin -> if new.val = '' then -> signal sqlstate '45000'; -> end if; -> end;$$ Query OK, 0 rows affected (0.01 sec) 

• Try inserting zero and an empty row into the column:

   mysql> delimiter ; mysql> insert into yar values(""); ERROR 1644 (45000): Unhandled user-defined exception condition mysql> insert into yar values(NULL); ERROR 1048 (23000): Column 'val' cannot be null mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec) mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec) 

Finally, shake yourself and remove the closest person who was responsible for selecting mysql over postgresql.

Answer 5

NULL is not equal to void. In MySQL, there is an additional byte with each column entry for storing information "is null." To save space, a column is often defined as “not null” to free this extra byte if zero status does not add anything to the data model.