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?

SQLite AUTOINCREMENT documentation - autoincrement is not ANSI, sequences were only recently made ANSI for handling sequential value generation. — OMG Ponies
– OMG Ponies, Commented Feb 11, 2012 at 21:07
what made you think they were the same? Each RDBMS seems to have it's own little twist, so you are going to run into these kind of issues. Here is sqlite documentation on AUTOINCREMENT: sqlite.org/autoinc.html — J Cooper
– J Cooper, Commented Feb 11, 2012 at 21:08

Vilius Sutkus '89 · Accepted Answer · 2012-02-11 21:07:49Z

10

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

In SQLite it's called AUTOINCREMENT, not AUTO_INCREMENT

answered Feb 11, 2012 at 21:07

Vilius Sutkus '89

8207 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Baker · Accepted Answer · 2012-02-11 21:07:14Z

6

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.

answered Feb 11, 2012 at 21:07

Mark Baker

213k34 gold badges354 silver badges391 bronze badges

Comments

cHao · Accepted Answer · 2012-02-11 21:06:36Z

5

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

answered Feb 11, 2012 at 21:06

cHao

87.1k21 gold badges147 silver badges178 bronze badges

2 Comments

Alex Over a year ago

ok I changed that but now I get the same error ...near "AUTOINCREMENT":

cHao Over a year ago

You may have to say like id INT NOT NULL PRIMARY KEY AUTOINCREMENT. I've never bothered with SQLite; i just knowhow to google. :)

aleroot · Accepted Answer · 2012-02-11 21:07:15Z

3

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.

answered Feb 11, 2012 at 21:07

aleroot

73k32 gold badges182 silver badges220 bronze badges

Comments

Mark Byers · Accepted Answer · 2012-02-11 21:06:25Z

2

No, they support a completely different set of features. The most significant difference is that SQLite uses dynamic data types whereas MySQL uses static data types, but there are many other differences too.

They do however both support a common subset of SQL, so it is possible to write some simple SQL statements that will work in both systems.

answered Feb 11, 2012 at 21:06

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

SQLite syntax not compatible with MySQL?

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related