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=gcdmw-win32@m.gmane.org

Re: DEFAULT values in NOT NULL columns

Hi,

> Like this, when u create a new data record, and dontspecify the value,
> it'll be filled with the defaultvalue of the column.

I understand that, but I thought MySQL would be so "nice"
to also replace a NULL by the default value of the column.

> And u cannot change a value to NULL after it got a value.

I didn't actually want to change the value to NULL, I
just wanted to cause MySQL to insert the default value!

How can I cause MySQL to change the value of a column to
the default value?


Anne

--
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

RE: DEFAULT values in NOT NULL columns

> -----Original Message-----
> From: Annemarie Mayer
>
> 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?

On a NOT NULL column mysql will give an error on a single row insert if you
try to explicitly insert null as you have seen. On a multiple row insert it
will insert the zero value for that column type in place of NULL and create
warnings.

> 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'?

For a date field the zero value is 0000-00-00.

>
> 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

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

--
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