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.

Answer 2

You're inserting an empty string, not NULL. The constraint is only against NULL values, and it would appear that your database is not coercing empty strings to NULL when it converts them to INT (which raises the additional question of why you're inserting string literals into INT columns...)

Answer 3

MySQL, how to disallow empty string:

1. Create your table:

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

2. Create your 'before insert' trigger to check for blankstring and disallow.

   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)
   

3. Try to insert null and blankstring into your 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, Grumble to self and smack the nearest person who was responsible for picking mysql over postgresql.

Answer 4

As Martin mentions, depends on your RDBMS. Oracle treats empty strings as NULLs while others do not. See this SO post.

Answer 5

NULL is not equal to emptiness. In MySQL, there is an additional byte with each column entry to hold the "is null" information. To save space, a column is often defined as "not null" to spare this extra byte if the null status doesn't add any thing to the data model.