Fw: DEFAULT values in NOT NULL columns

The NULL operant only means the field value cannot be empty. When a field is
maked with a default value, it's automatically changed to NOT NULL. Like
this, when u create a new data record, and dont specify the value, it'll be
filled with the default value of the column. And u cannot change a value to
NULL after it got a value. It must be a value of any kind (empty string, 0
or, for the non-null fields, any value)

----- Original Message -----
From: "Annemarie Mayer"
To:
Sent: Tuesday, February 01, 2005 6:41 PM
Subject: DEFAULT values in NOT NULL columns


>
> Hello list!
>
>
> I encountered some strange behaviour with MySQL 4.1.7 and 4.1.9 (on a
> Windows XP machine) concerning DEFAULT values in NOT NULL columns, and
> then inserting or updating (with) NULL.
>
> Example:
>
> CREATE TABLE Datetest (
> ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
> Dat DATE NOT NULL DEFAULT '2000-01-01',
>
> PRIMARY KEY (ID)
> ) ENGINE=INNODB;
>
> INSERT INTO Datetest VALUES (1, NULL);
> -> leads to an error "NULL not allowed in a NOT NULL column".
>
> INSERT INTO Datetest VALUES (1, '2004-12-12');
> UPDATE Datetest SET Dat = NULL WHERE ID = 1;
> -> leads to no error, and then
> SELECT * FROM Datetest;
> -> gives the values (1, '0000-00-00').
>
> First, why doesn't MySQL replace the NULL by the default value when
> inserting?
> Second, why does it process the UPDATE without an error if the INSERT
> throws
> one? (There is a warning.)
> Third, why does it insert its own default value '0000-00-00' instead of
the
> specified '2000-01-01'?
>
> BTW 1:
> INSERT INTO Datetest (ID) VALUES (10);
> -> inserts '2000-01-01' into the Dat column, but I need a solution for
> specifying all of the columns in the INSERT or UPDATE statement.
>
> BTW 2:
> The example has the same results with a numeric column instead of a DATE
> column.
>
>
> Thanks for any thoughts,
> Anne
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=msc2004@ubbi.com.br
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org