SQLite Primary Key column not auto incrementing

When developing with PHP, I frequently use SQLite databases since they’re very disposable and keeps from cluttering my MySQL server with a bunch of development/testing databases that may be abandoned or no longer used.
One of the problems I’ve had is when dealing with the Primary Key column and discovering that it for some reason won’t auto increment like it should.

So I did some testing and troubleshooting and finally figured it out.
Here’s a few “guidelines” that may help you out so you can avoid these issues too.

MY ENVIRONMENT:

I am using PHP 5.3.2 and PDO.
PDO is available with PHP as of PHP 5.1, but Sqlite3 is available as of PHP 5.3.0 which you can read more about at http://us.php.net/manual/en/book.sqlite3.php.

INTEGER not INT

Very often when writing PHP scripts, the option to choose between MySQL or SQLite database types comes into play. Also, many of us learn how to use a database with PHP using MySQL first and SQLite is a little different regarding the acceptable SQL syntax. MySQL will gladly accept INT as a substitution for INTEGER and SQLite won’t complain either, but in my experiences SQLite doesn’t see the two as being the same for some reason.

Take a look at this example: (You can try it yourself if you like)

$sql = "CREATE TABLE test_table (
id INT PRIMARY KEY,
name VARCHAR(50) DEFAULT NULL )";
This will create the table without any errors, but when you insert a new row, the id column won’t increment and will simply be empty. You can add NOT NULL to it, but it still doesn’t make any difference.

The other thing I noticed was that it wasn’t bias towards using an empty string value for this field when the data type was set to INT as in this example:

$sql = "INSERT INTO test_table (id, name) VALUES ('', 'JohnDoe')";
Now try it again and add AUTOINCREMENT to it like this:

$sql = "CREATE TABLE test_table (
id INT PRIMARY KEY AUTOINCREMENT,
name VARCHAR(50) DEFAULT NULL )";
Now all of the sudden you should get an error (if you have the error attribute set) similar to this:

'SQLSTATE[HY000]: General error: 1 AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY'
So this clearly shows that INT and INTEGER are NOT the same.
As soon as you change INT to INTEGER in your SQL syntax, the primary key works just fine and it doesn’t matter if the AUTOINCREMENT is present or not.

Specifying Field Lengths Will Cause Problems

The only field data types SQLite requires (at least in PHP) a field length for is the ‘VARCHAR’ type. From my understanding, it doesn’t enforce this length, but I may be wrong. Either way, specifying data lengths to other field types normally won’t cause any sort of errors, but they will be ignored.

However, in the case of the PRIMARY KEY field, it seems to have the same effect as using INT instead of INTEGER, even if the data type you specify IS INTEGER.
Here’s an example:

$sql = "CREATE TABLE test_table (
id INTEGER(5) PRIMARY KEY,
name VARCHAR(50) DEFAULT NULL )";
Now if you insert a row, you will see that the id column isn’t incrementing.
If you add AUTOINCREMENT to the end, you will see the SQLite error message I mentioned above.
So be sure to leave off the data length from your SQL syntax for the primary key field.

That pretty much sums it up and hopefully helps you avoid the headaches I had before figuring this out.

Additional SQLite SQL syntax caveats

Something I see used often in SQL syntax for MySQL is the ( ` ) character as in this example:

$sql = "INSERT INTO test_table (`id`, `name`) VALUES ('', 'JohnDoe')";
They look a lot like apostrophes, but they’re actually what’s called spacing acute characters. On a U.S. Keyboard, it’s the key just above the TAB key.
They have no real purpose (that I know of) other than just making it “nicer” looking.

The problem comes when trying to use that syntax in SQLite queries.
SQLite doesn’t like these characters and it will result in an error. This also was a lesson that took me a lot of wasted time to figure out, so just avoid them to begin with.

摘自：http://mydumbthoughts.wordpress.com/2010/06/12/sqlite-primary-key-column-not-auto-incrementing/