Really going crazy with NOT NULL

Suppose I set up the following table with 5.0.18-nt:

CREATE TABLE `leads` (
`id` int(11) NOT NULL auto_increment,
`uniqueId` varchar(40) default NULL,
`upcardkey` int(11) NOT NULL default '0',
`associatekey` int(11) NOT NULL default '0',
`date` varchar(16) default NULL,
`time` varchar(11) default NULL,
`sensor` int(3) NOT NULL default '0',
`heads` int(3) NOT NULL default '1',
`upskey` int(11) NOT NULL default '0',
`closed` int(3) NOT NULL default '0',
`howdidyouhear` int(11) NOT NULL default '0',
`bb` int(3) NOT NULL default '0',
`tor` int(3) NOT NULL default '0',
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


When I go to perform the following insert:

INSERT INTO leads( uniqueId, upcardkey, associatekey, date, time, sensor,
heads, upskey, howdidyouhear, bb, tor )
VALUES (
'-1|-1|7|2006-01-24 Tue|15:13:07|0|1|39', '', '7', '2006-01-24 Tue',
'15:15:05', '0', '1', '39', '3', '0', '0'
)

My insert fails:
MySQL said:

#1264 - Out of range value adjusted for column 'upcardkey' at row 1

This did not occur with MySQL < 5.0. Can someone clue me in on things here?
Is there a workaround I can employ such that my code would be compatible
with MYSQL 4.x and 5.x ? Thanks, Ike

Re: Really going crazy with NOT NULL

"Ike" wrote in message
news:kkwBf.5808$vU2.5208@newsread3.news.atl.earthlink.net...
> `upcardkey` int(11) NOT NULL default '0',
.. . .
> INSERT INTO leads( uniqueId, upcardkey, associatekey, date, time, sensor,
> heads, upskey, howdidyouhear, bb, tor )
> VALUES (
> '-1|-1|7|2006-01-24 Tue|15:13:07|0|1|39', '', '7', '2006-01-24 Tue',
> '15:15:05', '0', '1', '39', '3', '0', '0'
> )
.. . .
> #1264 - Out of range value adjusted for column 'upcardkey' at row 1

Looks like you're trying to put a zero-length string '' into an int column.
An empty string cast to an integer is not the same thing as a NULL, so it
does not use the default value you specified; it uses 0. By coincedence,
this is the same as your default value in this case, but if you have a
different value as the default, it still uses 0 when casting '' as an
integer.

> This did not occur with MySQL < 5.0. Can someone clue me in on things
> here?
> Is there a workaround I can employ such that my code would be compatible
> with MYSQL 4.x and 5.x ? Thanks, Ike

Yes -- specify legitimate integer values when inserting data into integer
columns.

Regards,
Bill K.