Skip navigation links

MySQL Forums :: General :: Not null field with default value


Advanced Search

Re: Not null field with default value
Posted by: Jay Alverson ()
Date: February 08, 2009 04:25PM

I see what you're saying, but I think the onus is on the user to make sure
things are being done correctly.

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> #  what's the current value for the SQL MODE ?
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            | 
+------------+
1 row in set (0.00 sec)

mysql> 
mysql> drop table if exists nn_test;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create table nn_test (
    -> the_string varchar(25) NOT NULL DEFAULT "nada");
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> insert into nn_test values
    -> (""),
    -> (NULL),
    -> (DEFAULT),
    -> ("hello");
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> 
mysql> show warnings;
+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 1048 | Column 'the_string' cannot be null | 
+---------+------+------------------------------------+
1 row in set (0.00 sec)

mysql> show errors;
Empty set (0.00 sec)

mysql> 
mysql> select * from nn_test;
+------------+
| the_string |
+------------+
|            | 
|            | 
| nada       | 
| hello      | 
+------------+
4 rows in set (0.00 sec)

mysql> 
mysql> notee

From the MySQL Manual
http://dev.mysql.com/doc/#refman

Chapter 1.8.6.2. Constraints on Invalid Data



The reason for using the preceding rules in non-strict mode is that we can't
check these conditions until the statement has begun executing. We can't just
roll back if we encounter a problem after updating a few rows, because the
storage engine may not support rollback. The option of terminating the
statement is not that good; in this case, the update would be “half done,”
which is probably the worst possible scenario. In this case, it's better to
“do the best you can” and then continue as if nothing happened.

>

Thanks, Jay

Options: ReplyQuote


Subject Written By Posted
Not null field with default value Tom Byars 02/08/2009 02:34PM
Re: Not null field with default value Jay Alverson 02/08/2009 04:25PM
Re: Not null field with default value Tom Byars 02/08/2009 04:52PM
Re: Not null field with default value Peter Brawley 02/08/2009 04:57PM
Re: Not null field with default value Jay Alverson 02/08/2009 07:50PM
Re: Not null field with default value Tom Byars 02/09/2009 06:55AM
Re: Not null field with default value Tom Byars 02/11/2009 06:25AM
Re: Not null field with default value Jay Alverson 02/09/2009 09:15AM
Re: Not null field with default value Tom Byars 02/10/2009 04:20AM
Re: Not null field with default value Jay Alverson 02/10/2009 07:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.