CODEWITHSUNDEEP
androidsqlite
Abhinav
Abhinav

Reputation: 1740

How to create a table with _id as composite primary key?

I am new to android sqlite.

Problem:

Create a table with three columns(namely column1,column2 and column3) all of type TEXT .Column1 and Column2 should together form a composite primary key.Also,this table will be used by CursorAdapter. So creating an _id field becomes necessary.

Till Now:

But I am unable to create the combination of both.

I have tried the below queries and both give errors:

1> CREATE TABLE Test ( _id INTEGER AUTOINCREMENT, column1 TEXT, column2 TEXT, column3 TEXT, PRIMARY KEY(_id ,column1,column2 ));

2>CREATE TABLE Test ( _id INTEGER , column1 TEXT, column2 TEXT, column3 TEXT, PRIMARY KEY(_id AUTOINCREMENT,column1,column2 ));

So please help me out in creating the table with the below mentioned feature i.e.

Upvotes: 2

Views: 882

Answers (3)

matt
matt

Reputation: 83

If you use the ROWID approach do you open yourself up to race conditions if multiple resolvers are calling your provider at once? For example let's say some app deletes row #5, shifting all the ROWIDs down by 1.. if you click to update that same row prior to your dataset being refreshed, you'll end up writing over row #6 since you're using row number and not unique IDs (IDs would prevent this since SQLiteDatabase is threadsafe).. It might be important to use unique IDs if that's true, even if it takes up one extra column.. would like to know for sure..

Upvotes: 0

Akash Moradiya
Akash Moradiya

Reputation: 3322

try this,

CREATE TABLE IF NOT EXISTS Test ( _id INTEGER, column1 TEXT, column2 TEXT, column3 TEXT, PRIMARY KEY(_id,column1,column2));

Upvotes: -1

CommonsWare
CommonsWare

Reputation: 1007369

So creating an _id AUTOINCREMENT field is mandatory.

Not exactly. The Cursor needs an _id INTEGER column that is unique. You could use an INTEGER PRIMARY KEY if you had one. If not, use ROWID:

SELECT ROWID AS _id, ...

where ... is the rest of your query. This allows you to leave your table schema alone, yet still satisfy the CursorAdapter requirement.

(and how I wish Google had pointed this out back in 2008...)

Upvotes: 3

Related Questions