auto_increment and not null

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

auto_increment and not null

Bruno S. Oliveira
Hi all,

I'm having some trouble in inserting data into a table which its
primary key is set as auto_increment and not null.
The table was created as follows:

CREATE TABLE te_representation (repres_id  INTEGER  AUTO_INCREMENT
NOT NULL ,layer_id  INTEGER ,geom_type  INTEGER ,geom_table  TEXT
,description  TEXT ,lower_x  REAL ,lower_y  REAL ,upper_x  REAL
,upper_y  REAL ,res_x  REAL ,res_y  REAL , num_cols  INTEGER ,num_rows
 INTEGER ,initial_time  TEXT ,final_time  TEXT , PRIMARY KEY
(repres_id) );
CREATE INDEX te_idx_representation ON te_representation(layer_id);

And I'm executing this query:

INSERT INTO te_representation (layer_id, geom_type, geom_table,
description,lower_x, lower_y, upper_x, upper_y, res_x, res_y,
num_cols, num_rows) VALUES( 1, 4, 'Points1', '',3.400000e+37,
3.400000e+37, -3.400000e+37, -3.400000e+37, 0.000000e+00,
0.000000e+00, 0, 0);

The sqlite3 complains for the repres_id may not be NULL. I know that
it will work if I remove the repres_id's not null constraint, but this
I wouldn't like to do.

Does the sqlite auto_increment work only with primary keys WITHOUT the
not null constraint? The same query on the same table worked on MySQL.

I would appreciate any idea to work through this.

Thanks in advance.

Best regards.

Bruno

--
/**
 * Bruno Silva de Oliveira
 * Bacharel em Ciência da Computação
 * Mestrando em Inteligência Computacional
 * http://www.inf.ufpr.br/brunoso/
 *
 * http://www.last.fm/user/bsoliveira/
 */
Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Eugene Wee
Hi Bruno,

Why not declare repres_id as INTEGER PRIMARY KEY? You can refer to the
documentation on CREATE TABLE:
http://www.sqlite.org/lang_createtable.html

Regards,
Eugene Wee

Bruno S. Oliveira wrote:

> Hi all,
>
> I'm having some trouble in inserting data into a table which its
> primary key is set as auto_increment and not null.
> The table was created as follows:
>
> CREATE TABLE te_representation (repres_id  INTEGER  AUTO_INCREMENT
> NOT NULL ,layer_id  INTEGER ,geom_type  INTEGER ,geom_table  TEXT
> ,description  TEXT ,lower_x  REAL ,lower_y  REAL ,upper_x  REAL
> ,upper_y  REAL ,res_x  REAL ,res_y  REAL , num_cols  INTEGER ,num_rows
> INTEGER ,initial_time  TEXT ,final_time  TEXT , PRIMARY KEY
> (repres_id) );
> CREATE INDEX te_idx_representation ON te_representation(layer_id);
>
> And I'm executing this query:
>
> INSERT INTO te_representation (layer_id, geom_type, geom_table,
> description,lower_x, lower_y, upper_x, upper_y, res_x, res_y,
> num_cols, num_rows) VALUES( 1, 4, 'Points1', '',3.400000e+37,
> 3.400000e+37, -3.400000e+37, -3.400000e+37, 0.000000e+00,
> 0.000000e+00, 0, 0);
>
> The sqlite3 complains for the repres_id may not be NULL. I know that
> it will work if I remove the repres_id's not null constraint, but this
> I wouldn't like to do.
>
> Does the sqlite auto_increment work only with primary keys WITHOUT the
> not null constraint? The same query on the same table worked on MySQL.
>
> I would appreciate any idea to work through this.
>
> Thanks in advance.
>
> Best regards.
>
> Bruno
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Bruno S. Oliveira
Hi Eugene and all,

On 6/28/07, Eugene Wee <[hidden email]> wrote:
> Why not declare repres_id as INTEGER PRIMARY KEY? You can refer to the
> documentation on CREATE TABLE:

Well, I am declaring it as INTEGER PRIMARY KEY. Or do you mean without
the NOT NULL and AUTO_INCREMENT?

I'm aware of the create table syntax. And according to it I can
declare a column as INTEGER  AUTO_INCREMENT NOT NULL. So I wonder why
this doesn't work (in MySQL it does!).

I just tried some more examples and discovered that the sqlite
complains only when one declares a column as auto_increment:

> create table t1 (a integer not null, b text, primary key(a));
> insert into t1 (b) values('bruno');
It works!

> create table t2 (a integer auto_increment not null, b text, primary key(a));
> insert into t2 (b) values('bruno');
Doesn't work!

Anyway, my program (which creates tables and generates queries like
these) must specify, for several tables, which columns must be auto
incremented. But, if you say to me that every primary key column
behaves as an auto_increment even if not declared so, my problems are
almost solved (I hope so!).

Thanks for the attention, Eugene.

Best regards,

Bruno

> http://www.sqlite.org/lang_createtable.html
>
> Regards,
> Eugene Wee
>
> Bruno S. Oliveira wrote:
> > Hi all,
> >
> > I'm having some trouble in inserting data into a table which its
> > primary key is set as auto_increment and not null.
> > The table was created as follows:
> >
> > CREATE TABLE te_representation (repres_id  INTEGER  AUTO_INCREMENT
> > NOT NULL ,layer_id  INTEGER ,geom_type  INTEGER ,geom_table  TEXT
> > ,description  TEXT ,lower_x  REAL ,lower_y  REAL ,upper_x  REAL
> > ,upper_y  REAL ,res_x  REAL ,res_y  REAL , num_cols  INTEGER ,num_rows
> > INTEGER ,initial_time  TEXT ,final_time  TEXT , PRIMARY KEY
> > (repres_id) );
> > CREATE INDEX te_idx_representation ON te_representation(layer_id);
> >
> > And I'm executing this query:
> >
> > INSERT INTO te_representation (layer_id, geom_type, geom_table,
> > description,lower_x, lower_y, upper_x, upper_y, res_x, res_y,
> > num_cols, num_rows) VALUES( 1, 4, 'Points1', '',3.400000e+37,
> > 3.400000e+37, -3.400000e+37, -3.400000e+37, 0.000000e+00,
> > 0.000000e+00, 0, 0);
> >
> > The sqlite3 complains for the repres_id may not be NULL. I know that
> > it will work if I remove the repres_id's not null constraint, but this
> > I wouldn't like to do.
> >
> > Does the sqlite auto_increment work only with primary keys WITHOUT the
> > not null constraint? The same query on the same table worked on MySQL.
> >
> > I would appreciate any idea to work through this.
> >
> > Thanks in advance.
> >
> > Best regards.
> >
> > Bruno
> >
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>


--
/**
 * Bruno Silva de Oliveira
 * Bacharel em Ciência da Computação
 * Mestrando em Inteligência Computacional
 * http://www.inf.ufpr.br/brunoso/
 *
 * http://www.last.fm/user/bsoliveira/
 */
Reply | Threaded
Open this post in threaded view
|

RE: auto_increment and not null

Griggs, Donald
Regarding:  "I'm aware of the create table syntax. And according to it I
can declare a column as INTEGER  AUTO_INCREMENT NOT NULL. So I wonder
why this doesn't work (in MySQL it does!)."


To underscore -- the difficulty:

Maybe it was just a typing mistake in your message, but according to
   http://sqlite.org/lang_createtable.html 
The keyword is AUTOINCREMENT and not AUTO_INCREMENT


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Bruno S. Oliveira
Hi Griggs and everyone,

Nop, the problem isn't the underscore. The sqlite works with both
keywords: AUTOINCREMENT and AUTO_INCREMENT.

Thanks for the attention!

Regards,

Bruno

On 6/28/07, Griggs, Donald <[hidden email]> wrote:

> Regarding:  "I'm aware of the create table syntax. And according to it I
> can declare a column as INTEGER  AUTO_INCREMENT NOT NULL. So I wonder
> why this doesn't work (in MySQL it does!)."
>
>
> To underscore -- the difficulty:
>
> Maybe it was just a typing mistake in your message, but according to
>    http://sqlite.org/lang_createtable.html
> The keyword is AUTOINCREMENT and not AUTO_INCREMENT
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>


--
/**
 * Bruno Silva de Oliveira
 * Bacharel em Ciência da Computação
 * Mestrando em Inteligência Computacional
 * http://www.inf.ufpr.br/brunoso/
 *
 * http://www.last.fm/user/bsoliveira/
 */
Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Trevor Talbot-2
In reply to this post by Bruno S. Oliveira
On 6/28/07, Bruno S. Oliveira <[hidden email]> wrote:

> Does the sqlite auto_increment work only with primary keys WITHOUT the
> not null constraint? The same query on the same table worked on MySQL.

"integer primary key" is special, and the only column "autoincrement"
can be applied to.  It's not a general-purpose sequence option, like
some other database engines.

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

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Bruno S. Oliveira
Hi,

On 6/28/07, Trevor Talbot <[hidden email]> wrote:
>
> "integer primary key" is special, and the only column "autoincrement"
> can be applied to.  It's not a general-purpose sequence option, like
> some other database engines.
>

Yep, I knew that.
The problem is that, with a table created like this:
sqlite> CREATE TABLE t1 (a INTEGER AUTO INCREMENT NOT NULL, b TEXT,
PRIMARY KEY (a));

An inserting like this:
sqlite> INSERT INTO t1 (b) VALUES ('bruno');

Doesn't work. The error is:
SQL error: t1.a may not be NULL

> See http://sqlite.org/autoinc.html
>

There's nothing about insertions in this link...
=(

Thanks!

Regards.

Bruno

> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>


--
/**
 * Bruno Silva de Oliveira
 * Bacharel em Ciência da Computação
 * Mestrando em Inteligência Computacional
 * http://www.inf.ufpr.br/brunoso/
 *
 * http://www.last.fm/user/bsoliveira/
 */
Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Trevor Talbot-2
In reply to this post by Bruno S. Oliveira
On 6/28/07, Bruno S. Oliveira <[hidden email]> wrote:

> CREATE TABLE te_representation (repres_id  INTEGER  AUTO_INCREMENT
> NOT NULL ,layer_id  INTEGER ,geom_type  INTEGER ,geom_table  TEXT
> ,description  TEXT ,lower_x  REAL ,lower_y  REAL ,upper_x  REAL
> ,upper_y  REAL ,res_x  REAL ,res_y  REAL , num_cols  INTEGER ,num_rows
>  INTEGER ,initial_time  TEXT ,final_time  TEXT , PRIMARY KEY
> (repres_id) );

To add to this, you're also getting bit by sqlite's loose parsing of
column type data.  It determines affinity by heuristically matching
the rest of the parameters (http://sqlite.org/datatype3.html section
2.1), but that means it accepts pretty much any text there.
"auto_increment" is having no effect in your column definition.

To get an id column that is the btree key, with implicit 64bit integer
datatype and not null, your two options are:

    create table foo(id integer primary key);
    create table foo(id integer, primary key(id));

To add the guarantee that automatically generated values will never be
reused, your only option is:

    create table foo(id integer primary key autoincrement);

No other syntax will work, including "auto_increment", and it is not
possible to create another column with the same characteristics using
the built-in "autoincrement".

Yeah, it's a bit of hack.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Igor Tandetnik
In reply to this post by Bruno S. Oliveira
Bruno S. Oliveira
<[hidden email]> wrote:
> The problem is that, with a table created like this:
> sqlite> CREATE TABLE t1 (a INTEGER AUTO INCREMENT NOT NULL, b TEXT,
> PRIMARY KEY (a));

Make it

CREATE TABLE t1 (a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);

The autoincrement column must be spelled precisely this way.

Igor Tandetnik

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Trevor Talbot-2
In reply to this post by Trevor Talbot-2
> To add the guarantee that automatically generated values will never be
> reused, your only option is:
>
>     create table foo(id integer primary key autoincrement);

Got involved testing this and forgot what I originally went to look
for.  You can add "not null" on the end of that, if you want.  It's
already implicitly not null, but it's still valid syntax and will work
as intended.

"integer primary key autoincrement" is the magical phrase that must
not be broken up.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Bruno S. Oliveira
Hi all,

Now I see the what's happening.
But what if I need to create a table with two primary keys and one
auto incremented (and I DO need that!)? Isn't this possible?

Thanks a lot Trevor and all. Thanks for the attention.

Best regards,

Bruno

On 6/28/07, Trevor Talbot <[hidden email]> wrote:

> > To add the guarantee that automatically generated values will never be
> > reused, your only option is:
> >
> >     create table foo(id integer primary key autoincrement);
>
> Got involved testing this and forgot what I originally went to look
> for.  You can add "not null" on the end of that, if you want.  It's
> already implicitly not null, but it's still valid syntax and will work
> as intended.
>
> "integer primary key autoincrement" is the magical phrase that must
> not be broken up.
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>


--
/**
 * Bruno Silva de Oliveira
 * Bacharel em Ciência da Computação
 * Mestrando em Inteligência Computacional
 * http://www.inf.ufpr.br/brunoso/
 *
 * http://www.last.fm/user/bsoliveira/
 */
Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Andrew Finkenstadt
On 6/28/07, Bruno S. Oliveira <[hidden email]> wrote:
>
> Hi all,
>
> Now I see the what's happening.
> But what if I need to create a table with two primary keys and one
> auto incremented (and I DO need that!)? Isn't this possible?
>

If by primary key you mean alternate (unique) key, then yes.


create table andy_loves_sqlite (
  pk integer not null primary key autoincrement,
  ak integer not null unique
);
Reply | Threaded
Open this post in threaded view
|

Re: auto_increment and not null

Bruno S. Oliveira
Thanks Andrew and everyone.

I'll see what I can get with it.

Thanks for your attention,

Bruno

On 6/28/07, Andrew Finkenstadt <[hidden email]> wrote:

> On 6/28/07, Bruno S. Oliveira <[hidden email]> wrote:
> >
> > Hi all,
> >
> > Now I see the what's happening.
> > But what if I need to create a table with two primary keys and one
> > auto incremented (and I DO need that!)? Isn't this possible?
> >
>
> If by primary key you mean alternate (unique) key, then yes.
>
>
> create table andy_loves_sqlite (
>   pk integer not null primary key autoincrement,
>   ak integer not null unique
> );
>


--
/**
 * Bruno Silva de Oliveira
 * Bacharel em Ciência da Computação
 * Mestrando em Inteligência Computacional
 * http://www.inf.ufpr.br/brunoso/
 *
 * http://www.last.fm/user/bsoliveira/
 */