AUTO_INCREMENT error

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

AUTO_INCREMENT error

Brandon Pimenta
I cannot use AUTO_INCREMENT. Here's my query:

CREATE TABLE test (
test_1 NOT NULL AUTO_INCREMENT
);

Running this query gives me "SQL error: near "AUTO_INCREMENT": syntax
error". What does this mean?

SQLite 3.6.12
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Rob Richardson-3
Don't you have to specify a column type for test_1?

RobR

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Brandon Pimenta
Sent: Friday, August 03, 2012 9:09 AM
To: [hidden email]
Subject: [sqlite] AUTO_INCREMENT error

I cannot use AUTO_INCREMENT. Here's my query:

CREATE TABLE test (
test_1 NOT NULL AUTO_INCREMENT
);

Running this query gives me "SQL error: near "AUTO_INCREMENT": syntax error". What does this mean?

SQLite 3.6.12
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Igor Tandetnik
In reply to this post by Brandon Pimenta
Brandon Pimenta <[hidden email]> wrote:
> I cannot use AUTO_INCREMENT. Here's my query:
>
> CREATE TABLE test (
> test_1 NOT NULL AUTO_INCREMENT
> );

First, it's AUTOINCREMENT, without underscore. Second, it can only appear after PRIMARY KEY.

> Running this query gives me "SQL error: near "AUTO_INCREMENT": syntax error". What does this mean?

It means that yours is not a syntactically valid SQL statement. The error message seems pretty clear to me.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Kees Nuyt
In reply to this post by Brandon Pimenta
On Fri, 3 Aug 2012 10:08:56 -0300, Brandon Pimenta
<[hidden email]> wrote:

>I cannot use AUTO_INCREMENT. Here's my query:
>
>CREATE TABLE test (
>test_1 NOT NULL AUTO_INCREMENT
>);
>
>Running this query gives me "SQL error: near "AUTO_INCREMENT": syntax
>error". What does this mean?

The keyword is AUTOINCREMENT, without underscore.
It will only work on INTEGER PRIMARY KEY columns.

http://www.sqlite.org/lang_createtable.html


--
Regards,

Kees Nuyt

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Brandon Pimenta
When using the SQL query

CREATE TABLE test (
test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
);

or

CREATE TABLE test (
test_1 INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT
);

I will get the same error.

On Fri, Aug 3, 2012 at 12:46 PM, Kees Nuyt <[hidden email]> wrote:

> On Fri, 3 Aug 2012 10:08:56 -0300, Brandon Pimenta
> <[hidden email]> wrote:
>
> >I cannot use AUTO_INCREMENT. Here's my query:
> >
> >CREATE TABLE test (
> >test_1 NOT NULL AUTO_INCREMENT
> >);
> >
> >Running this query gives me "SQL error: near "AUTO_INCREMENT": syntax
> >error". What does this mean?
>
> The keyword is AUTOINCREMENT, without underscore.
> It will only work on INTEGER PRIMARY KEY columns.
>
> http://www.sqlite.org/lang_createtable.html
>
>
> --
> Regards,
>
> Kees Nuyt
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Simon Slavin-3

On 3 Aug 2012, at 4:53pm, Brandon Pimenta <[hidden email]> wrote:

> When using the SQL query
>
> CREATE TABLE test (
> test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
> );
>
> or
>
> CREATE TABLE test (
> test_1 INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT
> );
>
> I will get the same error.

All INTEGER PRIMARY KEY columns automatically have AUTOINCREMENT.  You should not specify it yourself.

Also, SQLite will never itself assign a NULL to any of the values.  Though I can see that you might want NOT NULL in there to stop it being done by a program which assigns its own value to test_1.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Igor Tandetnik
In reply to this post by Brandon Pimenta
Brandon Pimenta <[hidden email]> wrote:
> CREATE TABLE test (
> test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
> );

Make it

INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

Though NOT NULL is redundant - PRIMARY KEY implies it.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Igor Tandetnik
In reply to this post by Simon Slavin-3
Simon Slavin <[hidden email]> wrote:
> All INTEGER PRIMARY KEY columns automatically have AUTOINCREMENT.  You should not specify it yourself.

There's a subtle difference in behavior with and without AUTOINCREMENT keyword. See

http://www.sqlite.org/autoinc.html

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Yuriy M. Kaminskiy
In reply to this post by Igor Tandetnik
Igor Tandetnik wrote:

> Brandon Pimenta <[hidden email]> wrote:
>> CREATE TABLE test (
>> test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
>> );
>
> Make it
>
> INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
>
> Though NOT NULL is redundant - PRIMARY KEY implies it.

Unlike other sql dialects, PRIMARY KEY in sqlite does not imply NOT NULL
constraint (see documentation).
However, INTEGER PRIMARY KEY is very special and unlike other column types - it
enforce type check and attempt to insert NULL will insert autoincremented value
instead.
sqlite> create table x(a text primary key);
sqlite> create table y(b integer primary key);
sqlite> insert into x values (NULL);
sqlite> insert into x values ('a');
sqlite> insert into x values (3);
sqlite> insert into x values (NULL);
sqlite> insert into y values (NULL);
sqlite> insert into y values ('a');
Error: datatype mismatch
sqlite> insert into y values (3);
sqlite> insert into y values (NULL);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(a text primary key);
INSERT INTO "x" VALUES(NULL);
INSERT INTO "x" VALUES('a');
INSERT INTO "x" VALUES('3');
INSERT INTO "x" VALUES(NULL);
CREATE TABLE y(b integer primary key);
INSERT INTO "y" VALUES(1);
INSERT INTO "y" VALUES(3);
INSERT INTO "y" VALUES(4);
COMMIT;

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Keith Medcalf
In reply to this post by Simon Slavin-3

That is not quite correct Simon.  INTEGER PRIMARY KEY is always "auto incrementing" in that a new key is always one larger than the current max() key in the table.  However, INTEGER PRIMARY KEY AUTOINCREMENT means that the key is always one larger than *any key that has ever existed* in the table.

In other words, INTEGER PRIMARY KEY AUTOINCREMENT keys are uniquely ascending and will never be re-used.  Without AUTOINCREMENT the key may be re-used. On a "different" record.


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Simon Slavin
> Sent: Friday, 03 August, 2012 10:00
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] AUTO_INCREMENT error
>
>
> On 3 Aug 2012, at 4:53pm, Brandon Pimenta <[hidden email]>
> wrote:
>
> > When using the SQL query
> >
> > CREATE TABLE test (
> > test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
> > );
> >
> > or
> >
> > CREATE TABLE test (
> > test_1 INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT
> > );
> >
> > I will get the same error.
>
> All INTEGER PRIMARY KEY columns automatically have AUTOINCREMENT.  You should
> not specify it yourself.
>
> Also, SQLite will never itself assign a NULL to any of the values.  Though I
> can see that you might want NOT NULL in there to stop it being done by a
> program which assigns its own value to test_1.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Brandon Pimenta
Thank you for this advice!

On Fri, Aug 3, 2012 at 9:24 PM, Keith Medcalf <[hidden email]> wrote:

>
> That is not quite correct Simon.  INTEGER PRIMARY KEY is always "auto
> incrementing" in that a new key is always one larger than the current max()
> key in the table.  However, INTEGER PRIMARY KEY AUTOINCREMENT means that
> the key is always one larger than *any key that has ever existed* in the
> table.
>
> In other words, INTEGER PRIMARY KEY AUTOINCREMENT keys are uniquely
> ascending and will never be re-used.  Without AUTOINCREMENT the key may be
> re-used. On a "different" record.
>
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
> > -----Original Message-----
> > From: [hidden email] [mailto:sqlite-users-
> > [hidden email]] On Behalf Of Simon Slavin
> > Sent: Friday, 03 August, 2012 10:00
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] AUTO_INCREMENT error
> >
> >
> > On 3 Aug 2012, at 4:53pm, Brandon Pimenta <[hidden email]>
> > wrote:
> >
> > > When using the SQL query
> > >
> > > CREATE TABLE test (
> > > test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
> > > );
> > >
> > > or
> > >
> > > CREATE TABLE test (
> > > test_1 INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT
> > > );
> > >
> > > I will get the same error.
> >
> > All INTEGER PRIMARY KEY columns automatically have AUTOINCREMENT.  You
> should
> > not specify it yourself.
> >
> > Also, SQLite will never itself assign a NULL to any of the values.
>  Though I
> > can see that you might want NOT NULL in there to stop it being done by a
> > program which assigns its own value to test_1.
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: AUTO_INCREMENT error

Keith Medcalf

The INTEGER PRIMARY KEY (with or without AUTOINCREMENT) is the rowid for the row in the table.  You are just giving it an explicit column name by declaring it as a column.  This allows you to, for example, refer to the rowid in referential contraints.  Even if you do not explicity declare an INTEGER PRIMARY KEY, each table always has one.  If you declare a PRIMARY KEY that is not an INTEGER PRIMARY KEY, that primary key is really just a UNIQUE constraint.  The "true" PRIMARY KEY is the implicit rowid.  This is because a table is stored in a btree structure and the rowid is the unique key into the btree which identifies a row.

Leaving a column/value unspecified in an insert operation is the same as explicitly specifying the column with a NULL value.  That is, if a table has two columns, col1 and col2, the following inserts are identical:  insert into table (col2) values (4); and insert into table (col1, col2) values (NULL, 4); and, they are processed identically and the materialized result will be identical.

Obviously every row must have a unique rowid (because it is the primary key for the underlying btree storage of the row), and the AUTOINCREMENT just modifies how the new explicitly named rowid is generated, if it is NULL on an insert.  

The default, used when you do not specify an explicitly named rowid column, is equivalent to rowid INTEGER PRIMARY KEY which means that a new rowid will be max(rowid)+1 from the table at the time the row is materialized.  So if you insert a row, then delete that row, then insert another row, the rowid's will be the same for the two different rows if at the time the insert was materialized max(rowid)+1 evaluated to the same result for both insert operations.  Obviously, there will not be duplicate rowid's, but there will not be a missing rowid corresponding to the deleted row.

If the rowid is defined as rowid INTEGER PRIMARY KEY AUTOINCREMENT, the insert/delete/insert will result in the the two inserted records having different increasing rowid's -- the row that was deleted will not have its unique rowid reused, and that rowid will no longer exist in the table.

The key concept is that AUTOINCREMENT changes the automatic rowid selection algorithm from using max(rowid) to using a hi-water mark as the basis for selection of the new rowid.

Whether or not you need to use autoincrement is dictated by the application/business logic.  If you are, for example, using the rowid externally (for example, syncronizing data with external datastores) then you may not want the rowid of deleted rows being implicitly reused (any rowid can always be explicitly used).  In other cases, it will simply not matter that different row instances might reuse rowid's.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Brandon Pimenta
> Sent: Saturday, 04 August, 2012 08:28
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] AUTO_INCREMENT error
>
> Thank you for this advice!
>
> On Fri, Aug 3, 2012 at 9:24 PM, Keith Medcalf <[hidden email]> wrote:
>
> >
> > That is not quite correct Simon.  INTEGER PRIMARY KEY is always "auto
> > incrementing" in that a new key is always one larger than the current max()
> > key in the table.  However, INTEGER PRIMARY KEY AUTOINCREMENT means that
> > the key is always one larger than *any key that has ever existed* in the
> > table.
> >
> > In other words, INTEGER PRIMARY KEY AUTOINCREMENT keys are uniquely
> > ascending and will never be re-used.  Without AUTOINCREMENT the key may be
> > re-used. On a "different" record.
> >
> >
> > ---
> > ()  ascii ribbon campaign against html e-mail
> > /\  www.asciiribbon.org
> >
> > > -----Original Message-----
> > > From: [hidden email] [mailto:sqlite-users-
> > > [hidden email]] On Behalf Of Simon Slavin
> > > Sent: Friday, 03 August, 2012 10:00
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] AUTO_INCREMENT error
> > >
> > >
> > > On 3 Aug 2012, at 4:53pm, Brandon Pimenta <[hidden email]>
> > > wrote:
> > >
> > > > When using the SQL query
> > > >
> > > > CREATE TABLE test (
> > > > test_1 INTEGER PRIMARY KEY NOT NULL AUTOINCREMENT
> > > > );
> > > >
> > > > or
> > > >
> > > > CREATE TABLE test (
> > > > test_1 INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT
> > > > );
> > > >
> > > > I will get the same error.
> > >
> > > All INTEGER PRIMARY KEY columns automatically have AUTOINCREMENT.  You
> > should
> > > not specify it yourself.
> > >
> > > Also, SQLite will never itself assign a NULL to any of the values.
> >  Though I
> > > can see that you might want NOT NULL in there to stop it being done by a
> > > program which assigns its own value to test_1.
> > >
> > > Simon.
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users