{"id":641,"date":"2012-03-22T15:06:35","date_gmt":"2012-03-22T15:06:35","guid":{"rendered":"http:\/\/www.gmarwaha.com\/blog\/?p=641"},"modified":"2014-09-15T15:43:20","modified_gmt":"2014-09-15T15:43:20","slug":"sqlite-auto-increment","status":"publish","type":"post","link":"https:\/\/www.gmarwaha.com\/blog\/2012\/03\/22\/sqlite-auto-increment\/","title":{"rendered":"SQLite Auto Increment"},"content":{"rendered":"<p>In SQLite, the primary key column get auto-incremented by default. So, my thought was that\u00a0there was\u00a0no necessity to set the\u00a0<code>autoincrement<\/code> flag explicitly in the\u00a0<code>create table<\/code> DDL. Then I\u00a0discovered a\u00a0hard truth.\u00a0When I\u00a0don&#8217;t set the <code>autoincrement<\/code> flag explicitly, it does\u00a0auto-increment, but there is a minor issue. It so happens that SQLite has two algorithms to perform auto-increment in primary keys &#8211; a default one where\u00a0you don&#8217;t explicitly set the flag and an other one where\u00a0you explicitly set the flag.<\/p>\n<p>To understand the glitch, let&#8217;s consider an example. Assume\u00a0that you have\u00a0a table with 5 rows with\u00a0<code>IDs<\/code> 1, 2, 3, 4\u00a0and\u00a05. The default algorithm increments the highest value in the <code>ID<\/code>\u00a0column by 1. If you add a new row to this table, it automatically gets an <code>ID<\/code> of 6. Now, let&#8217;s delete the row with <code>ID #6<\/code>. You are left in the same state you started &#8211; 5 rows with <code>IDs<\/code> 1, 2, 3, 4 and 5. Let&#8217;s add a new row once again. <strong>This new row again gets an ID of 6<\/strong>.<\/p>\n<p>This may have disastrous consequences or no consequences at all depending on your scenario. Generally, the autoincrement behavior that is expected out of a database is not this. I would expect the last row to get an ID of 7 instead since the PK column will not\u00a0be reused. That brings us to the other algorithm. If I explicitly set the <code>autoincrement<\/code> flag in the <code>create table<\/code> DDL, the row that was added last correctly gets an ID of 7 instead of 6. This behavior is consistent with other databases.\u00a0Not a big\u00a0issue at all depending on your scenario. But, just be warned.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQLite, the primary key column get auto-incremented by default. So, my thought was that\u00a0there was\u00a0no necessity to set the\u00a0autoincrement flag explicitly in the\u00a0create table DDL. Then I\u00a0discovered a\u00a0hard truth.\u00a0When I\u00a0don&#8217;t set the autoincrement flag explicitly, it does\u00a0auto-increment, but there is a minor issue. It so happens that SQLite has two algorithms to perform auto-increment in primary keys &#8211; a default one where\u00a0you don&#8217;t explicitly set the flag and an other one where\u00a0you explicitly set the flag. To understand the glitch, let&#8217;s consider an example. Assume\u00a0that you have\u00a0a table with 5 rows with\u00a0IDs 1, 2, 3, 4\u00a0and\u00a05. The default algorithm... <br \/><a class=\"moretag\" href=\"https:\/\/www.gmarwaha.com\/blog\/2012\/03\/22\/sqlite-auto-increment\/\">Continue reading...<\/a>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[],"class_list":["post-641","post","type-post","status-publish","format-standard","hentry","category-data"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/posts\/641","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/comments?post=641"}],"version-history":[{"count":6,"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/posts\/641\/revisions"}],"predecessor-version":[{"id":897,"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/posts\/641\/revisions\/897"}],"wp:attachment":[{"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/media?parent=641"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/categories?post=641"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gmarwaha.com\/blog\/wp-json\/wp\/v2\/tags?post=641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}