Database Schema, Default Value is NOW()

Question

I have database schema for users. It looks like...

CREATE TABLE `users` (

`id` int( 8 ) unsigned AUTO_INCREMENT,
`username` varchar( 255 ),
`password` varchar( 40 ),
`level` tinyint( 1 ) unsigned DEFAULT 1,
`time` datetime DEFAULT NOW(),
`email` varchar( 255 ),

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

There are six fields: id, username, password, level, time, email, but I want to insert only three of them - when user is registration: username, password and email. Rest of them will have default values.

Problem is that MySQL throws error: #1067 - Invalid default value for 'time'. Any ideas, guys?

score 6 · Accepted Answer · answered Mar 16 '11 at 08:28

Use CURRENT_TIMESTAMP, and change the column to timestamp.

It does the same, but works (as long as you can live with timestamp) - it is a limitation.

Discussions:

So your create becomes

CREATE TABLE `users` (
`id` int( 8 ) unsigned AUTO_INCREMENT,
`username` varchar( 255 ),
`password` varchar( 40 ),
`level` tinyint( 1 ) unsigned DEFAULT 1,
`time` timestamp DEFAULT CURRENT_TIMESTAMP,
`email` varchar( 255 ),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

score 0 · Answer 2 · answered Dec 20 '12 at 05:20

0

we can't make the "DEFAULT CURRENT_TIMESTAMP" when my attribute is "datetime" so we will change our attribute to "TIMESTAMP" like:

time * datetime* DEFAULT CURRENT_TIMESTAMP,

should be...

time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

answered Dec 20 '12 at 05:20

Pir Fahim Shah

10,505
1
82
81

score 0 · Answer 3 · answered Mar 16 '11 at 08:29

0

You need to use TimeStamp not DateTime

`time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

answered Mar 16 '11 at 08:29

codingbadger

42,678
13
95
110

score 0 · Answer 4 · answered Mar 16 '11 at 08:33

In general, MySQL does not support functions as default values. There's only one exception as far as I know: you can assign CURRENT_TIMESTAMP to a TIMESTAMP column. So you need to either change your column type (being aware that TIMESTAMP is not as powerful as DATETIME) or rely on a trigger to fill the value.

Database Schema, Default Value is NOW()

4 Answers4