List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 26 1999 2:47am
Subject:Re: UPDATE allows NULL when it shouldn't?
View as plain text  

>>>>> "Tani" == Tani Hosokawa <unknown@stripped> writes:

Tani> a similar thing is, if you do an alter tables and modify/change a column
Tani> to an enum, any values that aren't one of the enum values will be changed
Tani> to blanks, even if the column is 'not null'.

Tani> On Thu, 28 Oct 1999, Bill Dimm wrote:

>> >Description:
>> You can use UPDATE to assign NULL to a column which
>> has the NOT NULL property.  It seems to me that this
>> should generate an error, but it does not - it sets numeric
>> fields to 0, and string fields to "".
>> >How-To-Repeat:
>> create table testupdate ( field int not null );
>> insert into testupdate values ( 5 );
>> select * from testupdate;
>> update testupdate set field=NULL;
>> select * from testupdate;
>> drop table testupdate;


This is described in the MySQL manual;  As MySQL doesn't have
transactions and because of this can't rollback a faulty statement, it 
does the next best thing and tries to insert the best possible value
in case of errors.

PS: Sorry for the late reply;  I am still trying to catch up with my
    mails that piled up during my vacation.
UPDATE allows NULL when it shouldn't?Bill Dimm2 Nov
  • Re: UPDATE allows NULL when it shouldn't?Tani Hosokawa2 Nov
    • Re: UPDATE allows NULL when it shouldn't?Michael Widenius26 Nov