Hi Eugene and all, On 6/28/07, Eugene Wee <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -----------------------------------------------------------------------------
-- /** * 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/ */