database error

[denver bautista]

got an error when i execute this

CREATE TABLE IF NOT EXISTS `category` (

`id` INT( 11 ) NOT NULL ,
`name` VARCHAR( 100 ) NOT NULL ,
`timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`color` VARCHAR( 10 ) DEFAULT '#00bcd4',
`icon` VARCHAR( 100 ) NOT NULL DEFAULT 'https://mymonas.com/forum/category_icon/ic_question.png'
)ENGINE=INNODBAUTO_INCREMENT=9DEFAULTCHARSET=latin1;



MySQL said:

#1067 - Invalid default value for 'timestamp'


i tried to used CURRENT_TIMESTAMP now() and CURRENT_TIMESTAMP() but no luck getting error.

 

[Dead-i]

Hi,

The version of MySQL we are using only supports CURRENT_TIMESTAMP on TIMESTAMP fields, not DATETIME fields.

Thank you,

 

[denver bautista]

can you rewrite may database above. how can i fix that. sorry im beginner

 

[gamersc]

CREATE TABLE IF NOT EXISTS `category` (

`id` INT( 11 ) NOT NULL ,
`name` VARCHAR( 100 ) NOT NULL ,
`timestamp` CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`color` VARCHAR( 10 ) DEFAULT '#00bcd4',
`icon` VARCHAR( 100 ) NOT NULL DEFAULT 'https://mymonas.com/forum/category_icon/ic_question.png'
)ENGINE=INNODBAUTO_INCREMENT=9DEFAULTCHARSET=latin1;

there u go should work. btw i have not tested it out.

 

[denver bautista]

thanks for the reply but it wont work

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' at line 5

 

[shopnick]

tu nhien bi xoa

 

[essellar]

That line should probably have read:

`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

Alternatively, you can keep the datetime field type and use NOW() as the value.