CODEWITHSUNDEEP
mysql
David
David

Reputation: 20063

MySQL query datetime default

I've got the SQL query below that seems to be throwing an error but I', really stuck in where the syntax error is. I think it's on the field5 but I'm not 100% if I'm using default datetime correctly.

CREATE TABLE mytable (field0 int(10) unsigned NOT NULL auto_increment primary key,
field2 DATETIME NOT NULL,
field3 int(1) unsigned default 0,
field4 int(10) NOT NULL,
field5 DATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

Can anyone whose better at SQL see where the syntax error is? :)

Thanks

Upvotes: 1

Views: 451

Answers (3)

Jaydee
Jaydee

Reputation: 4158

Field 5 - TIMESTAMP is a type and so is DATETIME, use one or the other not both.

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Upvotes: 3

Devart
Devart

Reputation: 121922

CREATE TABLE mytable30(
  `key` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  field2 DATETIME NOT NULL,
  field3 INT(1) UNSIGNED DEFAULT 0,
  field4 INT(10) NOT NULL,
  field5 TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)

It is better to rename key field. INT(1) - should it be like this INT(11)?

Upvotes: 2

Tommy
Tommy

Reputation: 1267

You cannot use Datetime and Timestamp, you have to choose one.

Upvotes: 0

Related Questions