SQLite AUTO_INCREMENT id field not working

Question

I am trying to create a database using python to execute the SQL commands (for CS50x problem set 7).

I have created a table with an id field set to AUTO_INCREMENT, but the field in the database is populated only by NULL values. I just want it to have an incrementing id starting at 1.

I've tried searching online to see if I'm using the right syntax and can't find anything obvious, nor can I find someone else with a similar problem, so any help would be much appreciated.

Here is the SQL command I am running:

# For creating the table
db.execute("""
            CREATE TABLE students (
                id INTEGER AUTO_INCREMENT PRIMARY KEY,
                first_name VARCHAR(255) NOT NULL,
                middle_name VARCHAR(255) DEFAULT (NULL),
                last_name VARCHAR(255) NOT NULL,
                house VARCHAR(10),
                birth INTEGER
            );
       """)

# An example insert statement
db.execute("""
            INSERT INTO students (
                first_name,
                middle_name,
                last_name,
                house,
                birth
            )
            VALUES (
                ?, ?, ?, ?, ?
            );
    """, "Harry", "James", "Potter", "Gryffindor", 1980)

Here is a screenshot of the database schema shown in phpliteadmin :

And here is a screenshot of the resulting database:

Answer 1

My guess is that you are using SQLite with phpliteadmin and not MySql, in which case this:

id INTEGER AUTO_INCREMENT PRIMARY KEY

is not the correct definition of the auto increment primary key.

In fact, the data type of this column is set to INTEGER AUTO_INCREMENT, as you can see in phpliteadmin, which according to 3.1. Determination Of Column Affinity, has INTEGER affinity.
Nevertheless it is the PRIMARY KEY of the table but this allows NULL values.

The correct syntax to have an integer primary key is this:

id INTEGER PRIMARY KEY AUTOINCREMENT 

Answer 2

This cannot happen, if your statements are executed correctly.

I notice that you are not checking for errors in your code. You should be doing that!

My guess is that the table is already created without the auto_increment attribute. The create table is generating an error and you are inserting into the older version.

You can fix this by dropping the table before you create it. You should also modify the code to check for errors.