NOT NULL Why can I insert a record with no value?

[KevCo]

I am under the impression that a field that is marked NOT NULL means that when inserting a record, that field MUST have a value.
I have found that's not the case with my DB.
When I insert a record, leaving the field in question blank, mysql accepts it. I would rather it warn me of the empty field. I have searched but I cannot find a good answer for this.

[atoms]

Do you have a default value set for that field? If so, MySQL will insert that value when it is not given a value.

Can you show us the output from a DESCRIBE tablename; for the table? (here is an example)


mysql> describe people;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+

[KevCo]

Not quite the way you have it, but here it is:

Field Type Null Key Default Extra
name_id int(5) NO PRI NULL auto_increment
date_modified timestamp NO CURRENT_TIMESTAMP
f_name varchar(75) NO -
l_name varchar(75) NO NULL
birthday varchar(15) NO NULL
anniversary varchar(15) NO NULL

[atoms]

mysql> ALTER TABLE people ADD test_not_null int NOT NULL;
mysql> describe people;
+---------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| test_not_null | int(11) | NO | | NULL | |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> INSERT INTO people VALUES ('','','','');
Query OK, 1 row affected, 4 warnings (0.01 sec)

mysql> select * from people;
+----+---------------------+---------------------+---------------+
| id | created_at | updated_at | test_not_null |
+----+---------------------+---------------------+---------------+
| 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0 |
+----+---------------------+---------------------+---------------+
1 row in set (0.00 sec)

but

mysql> INSERT INTO people VALUES ('','','',NULL);
ERROR 1048 (23000): Column 'test_not_null' cannot be null

It looks like MySQL interprets an empty string as 0, not as null. You are probably aware that null (or nil in some languages such as ruby) is not the same as zero.

[r2b2]

Strange - most databases I've come across would treat '' as an empty string which would be the equivalent of null.

Guess you'll need to check your input before putting it in the database...