SQLite syntax not compatible with MySQL?

Question

I'm using PDO and trying to make my application support both MySQL and SQLite, but in sqlite I get this error when I try to import my database schema:

SQLSTATE[HY000]: General error: 1 near "AUTO_INCREMENT": syntax error

The query looks like this:

CREATE TABLE events (

  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL,
  title VARCHAR(64) NOT NULL,
  description LONGTEXT,
  starttime DATETIME DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY(id),
  KEY name(name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

(and it works in a MySQL database.)

I don't understand what the problem is here? Shouldn't both database systems be compatible?

Answer 1

http://www.sqlite.org/autoinc.html

In SQLite it's called AUTOINCREMENT, not AUTO_INCREMENT

Answer 2

They should be compatible as regards the ANSI SQL standards, and all SQL databases should adhere to that. However, AutoIncrement is not a part of that standard, but an extra feature implemented by some databases (including MySQL). Not all databases provide that feature, or may provide it in a different manner, or with different syntax.

Answer 3

AUTO_INCREMENT is MySQL-specific. SQLite apparently has a similar thing, AUTOINCREMENT.

Answer 4

Unfortunately though SQL should be a standard, each database implementation is different and have its own peculiarities, so you have to arrange your Query to make it work on SQLite.