I'm completely new to databases and SQL.
I have a column marked NOT NULL.
When I forget to give a value to that column when doing an INSERT, it inserts a 0 value in that column.
Is there anything I can do to make it return an error instead of changing the NULL to 0?
-
1Do you have a default on that column?Martin Smith– Martin Smith2010-12-03 14:04:05 +00:00Commented Dec 3, 2010 at 14:04
-
Is it an integer column? Are you not passing a value at all, or you believe the value is empty?Jason McCreary– Jason McCreary2010-12-03 14:04:32 +00:00Commented Dec 3, 2010 at 14:04
-
@Jason McCreary Yep, it's an int. It's when I forget that column in the INSERT statement so no value gets passed to it.Plain Jane– Plain Jane2010-12-03 14:07:52 +00:00Commented Dec 3, 2010 at 14:07
Add a comment
|
3 Answers
You want to have a look at the SQL_MODE configuration. It allows you to define how strict MySQL handles such things. By default it is pretty lenient which is not what I usually want. It also depends on the data type, especially with Dates it is less than optimal by default.
I personally go for STRICT_ALL_TABLES usually.
See the MySQL manual (5.0) here:
1 Comment
Plain Jane
I did "SET sql_mode = 'STRICT_ALL_TABLES';" and now it returns an error message whenever I forget a NOT NULL column. Muchas Gracias.
There are two possible reasons for this:
- You have a default value defined for that column
- Your column is
INT NOT NULLand castsNULLtoINT, which results in0
The second situation seems to be the case here, obviously. If you want to get an error instead, you could change the SQL_MODE to be strict. An other possibility is to do the input validation in your progam rather than leaving it to SQL.
