{"action":"created","issue":{"url":"https://api.github.com/repos/coleifer/peewee/issues/805","labels_url":"https://api.github.com/repos/coleifer/peewee/issues/805/labels{/name}","comments_url":"https://api.github.com/repos/coleifer/peewee/issues/805/comments","events_url":"https://api.github.com/repos/coleifer/peewee/issues/805/events","html_url":"https://github.com/coleifer/peewee/issues/805","id":124626851,"number":805,"title":"Some troubles with SQLite autoincrementing primary key.","user":{"login":"JoanSoConfused","id":16522559,"avatar_url":"https://avatars.githubusercontent.com/u/16522559?v=3","gravatar_id":"","url":"https://api.github.com/users/JoanSoConfused","html_url":"https://github.com/JoanSoConfused","followers_url":"https://api.github.com/users/JoanSoConfused/followers","following_url":"https://api.github.com/users/JoanSoConfused/following{/other_user}","gists_url":"https://api.github.com/users/JoanSoConfused/gists{/gist_id}","starred_url":"https://api.github.com/users/JoanSoConfused/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JoanSoConfused/subscriptions","organizations_url":"https://api.github.com/users/JoanSoConfused/orgs","repos_url":"https://api.github.com/users/JoanSoConfused/repos","events_url":"https://api.github.com/users/JoanSoConfused/events{/privacy}","received_events_url":"https://api.github.com/users/JoanSoConfused/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-01-03T03:41:12Z","updated_at":"2016-01-03T15:03:36Z","closed_at":"2016-01-03T15:03:36Z","body":"I am new to the peewee and I am using peewee 2.7.4 with Python 3.5.0 and with SQLite and I am very confused about SQLite autoincrementing primary keys on tables.\r\n\r\nThe peewee documentations says \"peewee will automatically add an auto-incrementing integer primary key field named id.\"\r\n\r\nI am using code based on some documentation sample code:\r\n```\r\n#!/usr/bin/env python3.5\r\n\r\nfrom peewee import *\r\n\r\ndb = SqliteDatabase('people.db')\r\n\r\nclass Person(Model):\r\n    name = CharField()\r\n    birthday = DateField()\r\n    is_relative = BooleanField()\r\n\r\n    class Meta:\r\n        database = db # This model uses the \"people.db\" database.\r\n\r\ndb.connect()\r\n\r\ndb.create_tables([Person], True)\r\n\r\nperson = Person.create(name='Joan', birthday='1970-01-01', is_relative=False)\r\nperson.save(force_insert=True)\r\n```\r\n\r\nSo class Person has no primary key field so peewee should add one I think.\r\n\r\nWhen I run the code it dies: \"sqlite3.IntegrityError: UNIQUE constraint failed: person.id\"\r\n\r\nWhen I inspect the person table I see the id field that peewee added does not have autoincrement!\r\n\r\n```\r\nsqlite> .schema person\r\nCREATE TABLE \"person\" (\"id\" INTEGER NOT NULL PRIMARY KEY, \"name\" VARCHAR(255) NOT NULL, \"birthday\" DATE NOT NULL, \"is_relative\" SMALLINT NOT NULL);\r\nsqlite> \r\n```\r\n\r\nI do not know what is bug here! Should id column have autoincrement? Or should documentations be clearer about specialistic SQLite behaviors?\r\n\r\nYour friend,\r\nJoan"},"comment":{"url":"https://api.github.com/repos/coleifer/peewee/issues/comments/168504439","html_url":"https://github.com/coleifer/peewee/issues/805#issuecomment-168504439","issue_url":"https://api.github.com/repos/coleifer/peewee/issues/805","id":168504439,"user":{"login":"coleifer","id":119974,"avatar_url":"https://avatars.githubusercontent.com/u/119974?v=3","gravatar_id":"","url":"https://api.github.com/users/coleifer","html_url":"https://github.com/coleifer","followers_url":"https://api.github.com/users/coleifer/followers","following_url":"https://api.github.com/users/coleifer/following{/other_user}","gists_url":"https://api.github.com/users/coleifer/gists{/gist_id}","starred_url":"https://api.github.com/users/coleifer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/coleifer/subscriptions","organizations_url":"https://api.github.com/users/coleifer/orgs","repos_url":"https://api.github.com/users/coleifer/repos","events_url":"https://api.github.com/users/coleifer/events{/privacy}","received_events_url":"https://api.github.com/users/coleifer/received_events","type":"User","site_admin":false},"created_at":"2016-01-03T15:03:36Z","updated_at":"2016-01-03T15:03:36Z","body":"You don't need the `force_insert=True` since you are using an auto-incrementing primary key. That might be the first problem.\r\n\r\n> When I inspect the person table I see the id field that peewee added does not have autoincrement!\r\n\r\nSQLite and autoincrement is more involved than you might think. There's a reason peewee doesn't specify `AUTOINCREMENT` with SQLite, described here:\r\n\r\nhttps://www.sqlite.org/autoinc.html\r\n\r\nPeewee instead will use the unique `rowid` which uses the algorithm MAX(rowid) + 1 to generate new rowids, rather than ensuring they are unique across deletions, etc."}}