CODEWITHSUNDEEP
javasqlite
user2746120
user2746120

Reputation:

Is it posible to have AUTOINCREMENT without setting it as PRIMARY KEY In ANDROID SQLite

I have tried with INTEGER AUTOINCREMENT while creating Table in Android Sqlite. But it shows error. But it works fine while giving INTEGER PRIMARY KEY AUTOINCREMENT.

My requirement is that, the column with AUTOINCREMENT shouldn't be a primary key.

db.execSQL("CREATE TABLE " + Table_KOT_ITEMS_TEMP + "(" + col_Item_Name
                + " varchar(40) NOT NULL," + col_Ref_No
                + " int(11) NOT NULL DEFAULT '0'," + col_Table_No
                + " varchar(6) NOT NULL DEFAULT '0'," + col_Kot_No
                + " int(11) NOT NULL DEFAULT '0'," + col_Main_Item_Code
                + " varchar(10) NOT NULL," + col_Item_Type
                + " varchar(10) NOT NULL," + col_Item_Amount
                + " decimal(10,2) DEFAULT '0.00'," + col_Item_Qty
                + " decimal(9,3) NOT NULL DEFAULT '0.000'," + col_No_Of_Persons
                + " varchar(6) NOT NULL," + col_Dum_Unique
                + " varchar(25) NOT NULL," + col_CheckBox
                + " char(5) NOT NULL," + col_Item_Auto_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT)");

Upvotes: 1

Views: 775

Answers (3)

Exceptional
Exceptional

Reputation: 3004

yeah, It's pretty difficult to achieve that, the only option is to give it as INTEGER PRIMARY KEY AUTOINCREMENT...

Upvotes: 0

Johannes Kuhn
Johannes Kuhn

Reputation: 15163

Not directly. But you can create a trigger that does that.

 -- Create table
 CREATE TABLE testtbl (
     id INTEGER PRIMARY KEY,
     autoincr DEFAULT NULL,
     otherrow TEXT -- and maybe more
 );
 -- Create helper table
 CREATE TABLE sequence_helper (
     name TEXT PRIMARY KEY COLLATE NOCASE,
     seq INTEGER DEFAULT 1
 );
 -- Fill in the starting number
 INSERT INTO sequence_helper VALUES ('testtbl', 1);
 -- Create the trigger
 CREATE TRIGGER autoincr_testtbl AFTER INSERT ON testtbl FOR EACH ROW BEGIN
     UPDATE testtbl SET 
         autoincr = (SELECT seq FROM sequence_helper WHERE name = 'testtbl')
         WHERE rowid = new.rowid;
     UPDATE sequence_helper SET seq = seq+1 WHERE name = 'testtbl';
 END;

You can probably improve this by checking if the autoincr column is NULL, but you should get the basic idea.

Upvotes: 2

CL.
CL.

Reputation: 180070

In SQLite, an AUTOINCREMENT column must be declared as INTEGER PRIMARY KEY.

Upvotes: 0

Related Questions