Need help with mysql error: Invalid default value for 'account_id'

Hi there,

Hope somebody can help me.

I'm trying to create database tables and got the following error.

[MYSQL]
SQL query:

CREATE TABLE `project` (
`project_id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT ,
`account_id` BIGINT( 20 ) NOT NULL DEFAULT '',
`project_name` VARCHAR( 255 ) NOT NULL DEFAULT '',
`admin_email` VARCHAR( 255 ) NOT NULL DEFAULT '',
`autoresponder_email` VARCHAR( 255 ) NOT NULL DEFAULT '',
`number_of_Answers` INT( 11 ) NOT NULL DEFAULT '0',
`Answers` TEXT NOT NULL ,
`create_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ( `project_id` )
);

MySQL said:

#1067 - Invalid default value for 'account_id'
[/MYSQL]

Here is the content in the database.sql that produced the error.

[MYSQL]
CREATE TABLE `account` (
`account_id` bigint(20) NOT NULL auto_increment,
`username` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`create_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`account_id`)
);

CREATE TABLE `project` (
`project_id` bigint(20) NOT NULL auto_increment,
`account_id` bigint(20) NOT NULL default '',
`project_name` varchar(255) NOT NULL default '',
`admin_email` varchar(255) NOT NULL default '',
`autoresponder_email` varchar(255) NOT NULL default '',
`number_of_Answers` int(11) NOT NULL default '0',
`Answers` text NOT NULL,
`create_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`project_id`)
) ;

CREATE TABLE `stats` (
`project_id` bigint(20) NOT NULL default '0',
`account_id` bigint(20) NOT NULL default '',
`question_id` bigint(20) NOT NULL default '',
`ip` bigint(20) NOT NULL default '0',
`answered` int(20) NOT NULL default '0',
`converted` int(20) NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00'
) ;
[/MYSQL]

Thanks for looking at this.

Jimmy

Perhaps this is because you are trying to insert an empty string into a column that has a NOT NULL constraint. Either provide an INTEGER value or remove the constraint.

Even if u remove the constraint we will get the same old error message like
---> Invalid dafault_value for 'account_id',So,that's not the cause for this.
we can not provide an empty string as default value for a column of type integer.
If u can observe in ur 'project' table u have a column with the name 'number_of_Answers' and with the datatype as INT for that u have provided 0 as default value,not an empty string.We have to provide 0 as defalut value(quotes are optional) for all columns of type INTEGER.
(if we provide default value as ' ',It's considering that as a string not an integer as per my knowledge).


CREATE TABLE project (
`project_id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT,

`account_id` BIGINT( 20 ) NOT NULL DEFAULT '0' or else
`account_id` BIGINT( 20 ) NOT NULL DEFAULT 0,

`project_name` VARCHAR( 255 ) NOT NULL DEFAULT '',
`admin_email` VARCHAR( 255 ) NOT NULL DEFAULT '',
`autoresponder_email` VARCHAR( 255 ) NOT NULL DEFAULT '',
`number_of_Answers` INT( 11 ) DEFAULT '0 ',
`Answers` TEXT NOT NULL ,
`create_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ( `project_id` )
);

Hope it will be useful If I am not wrong.