runs in Postgres, doesn't run in MySQL

Question

CREATE TABLE items (
    id serial primary key,
    author_id int references users,
    body text,
    created timestamp default (current_timestamp at time zone 'utc')
);

Why?

Answer 1

MySQL uses a slightly different syntax, so a straight copy / paste will not work, here's an example of what the create statement will look like for MySQL:

CREATE TABLE  `test`.`item` (
  `id` int(4) NOT NULL auto_increment,
  `author_id` int(11) default NULL,
  `body` text,
  `created` datetime default NULL,
  PRIMARY KEY  (`id`),
CONSTRAINT `FK_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`author_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Hope this helps.

--schiz

Answer 2

Because you can't really copypasta SQL between two different engines and expect it to work. ISTR that MySQL has no idea what a Serial field is.

The syntax between database engines is vastly different.