List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 8 2005 11:54pm
Subject:Re: default values
View as plain text  
Jeff Smelser wrote:

> On Tuesday 08 February 2005 03:32 pm, Emmett Bishop wrote:
> 
> 
>>is it possible to force a field to be NOT NULL but not
>>have any default value (I.E the insert statement must
>>explicitly provide data for the field in question)?
> 
> 
> of course: col1 int not null
> 
> Jeff

Except that ALL columns in mysql (prior to 5.0.2) have defaults.  See the 
manual <http://dev.mysql.com/doc/mysql/en/constraint-invalid-data.html>.

For example:

   mysql> CREATE TABLE deftest (col1 INT NOT NULL, col2 CHAR(1));
   Query OK, 0 rows affected (0.00 sec)

This works as expected

   mysql> INSERT INTO deftest VALUES (NULL, 'a');
   ERROR 1048: Column 'col1' cannot be null

but this

   mysql> INSERT INTO deftest (col2) VALUES ('b');
   Query OK, 1 row affected (0.01 sec)

and this

   mysql> INSERT INTO deftest VALUES (3, 'c'), (NULL, 'd');
   Query OK, 2 rows affected (0.01 sec)
   Records: 2  Duplicates: 0  Warnings: 1

may surprise you.

   mysql> SELECT * FROM deftest;
   +------+------+
   | col1 | col2 |
   +------+------+
   |    0 | b    |
   |    3 | c    |
   |    0 | d    |
   +------+------+
   3 rows in set (0.00 sec)

Michael
Thread
ERROR 1189 (08S01): Net error reading from masterOlivier Kaloudoff8 Feb
  • Re: ERROR 1189 (08S01): Net error reading from masterGleb Paharenko8 Feb
  • Re: ERROR 1189 (08S01): Net error reading from masterOlivier Kaloudoff8 Feb
    • Re: ERROR 1189 (08S01): Net error reading from masterBastian Balthazar Bux8 Feb
      • default valuesEmmett Bishop8 Feb
        • Re: default valuesJeff Smelser8 Feb
          • Re: default valuesMichael Stassen9 Feb
      • Re: ERROR 1189 (08S01): Net error reading from masterOlivier Kaloudoff8 Feb