CODEWITHSUNDEEP
pythonsqlsqlitecs50
strava
strava

Reputation: 765

SQLite AUTO_INCREMENT id field not working

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 :

Database schema shown in phpliteadmin

And here is a screenshot of the resulting database:

Resulting database shown in phpliteadmin

Upvotes: 1

Views: 7833

Answers (3)

Alec
Alec

Reputation: 1

The correct syntax of SQLite CREATE statement is:

CREATE TABLE students (
    id INTEGER,
    first_name VARCHAR(255) NOT NULL,
    middle_name VARCHAR(255) DEFAULT (NULL),
    last_name VARCHAR(255) NOT NULL,
    house VARCHAR(10),
    birth INTEGER,
    PRIMARY KEY(id AUTOINCREMENT)
)

Upvotes: 0

forpas
forpas

Reputation: 164064

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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.

Upvotes: 1

Related Questions