5

I have the below SQL I am trying to use to create a table and some columns. As part of it, I want two of the columns to autoincrement an integer. When I try using the below code it gives me an error.

    CREATE TABLE IF NOT EXISTS 'tasks' (
'rowID' INTEGER, 
'gID' INTEGER, 
'task' TEXT, 
'status' TEXT, 
'position' INTEGER, 
'updated' INTEGER, 
'inlist' TEXT, 
'deleted' TEXT, 
PRIMARY KEY AUTOINCREMENT ('rowID','position')
)

When I remove the keyword "AUTOINCREMENT" from the SQL it works fine.

Is it possible to have two autoincrementing columns? If not, is there a way I can have one column automatically take the value from the other (auto-incrementing) column as its being inserted?

Thank you

levi
  • 23,693
  • 18
  • 59
  • 73

2 Answers2

10

You can't have two autoincrement fields. You should use a single autoincrement field. Given that both fields would always have the same value for every row, there's no reason to have to such fields anyway.

Billy ONeal
  • 104,103
  • 58
  • 317
  • 552
  • 3
    I need two fields with the same values initially, but the "position" field will be updated at a later time, so I do need two separate values. Is there a way I can have the "position" field automatically take the value from "rowID" as its being inserted? – levi Sep 08 '11 at 18:10
  • @levi: As far as I am aware you can't update the value of an auto-increment field. Nor does having a primary key defined by a changable value make any sense. – Billy ONeal Sep 08 '11 at 18:33
5

I need two fields with the same values initially, but the "position" field will be updated at a later time, so I do need two separate values. Is there a way I can have the "position" field automatically take the value from "rowID" as its being inserted

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

Try using an after-insert trigger, setting colB = to colA's value. ColA is the auto-incremented value.

Tim
  • 8,669
  • 31
  • 105
  • 183