CODEWITHSUNDEEP
pythonmysqlsqlsqlite
rockets4all
rockets4all

Reputation: 876

sqlite not auto incrementing

When I use the following command to create my table in SQLite3 it does not auto increment ID nor does it make USERNAME a primary key as I can enter multiple users with the same USERNAME and it will input them without issues.

Command:

CREATE TABLE 'users' (
    'id' INTEGER AUTO INCREMENT, 
    'username' TEXT NOT NULL, 
    'hash' TEXT, 
    PRIMARY KEY ('id', 'username')
);

Here is an output from a console to show what it is doing, as you can tell it let me enter the username cat twice and did not assign a value to id as it should have according to auto increment:

sqlite> CREATE TABLE 'users' ('id' INTEGER AUTO INCREMENT, 'username' TEXT NOT NULL, 'hash' TEXT, PRIMARY KEY ('id', 'username'));

sqlite> insert into users (username, hash) values ('cat', 'dog2');

sqlite> insert into users (username, hash) values ('cat', 'dog2');

sqlite> select * from users;                                                                                                                                     
id          username    hash      
----------  ----------  ----------

            cat         dog2      
            cat         dog2

Upvotes: 1

Views: 268

Answers (1)

Michael
Michael

Reputation: 5335

It's AUTOINCREMENT, not AUTO INCREMENT. Also, if the field in the SQLite table has the AUTOINCREMENT attribute, it should be also PRIMARY KEY.

CREATE TABLE 'users' (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, hash TEXT);

You can't use the field with AUTOINCREMENT attribute as a part of compound key.

Upvotes: 1

Related Questions