Android composite primary key?

Question

Can anyone tell me how to declare a composite primary key in Android 1.6 which includes an autoincrement _id column? I'm not sure of the syntax. I've ended up just enforcing it in Java when I try to add values (where registrationNumber + date has to be unique in the table):

            Cursor fuelUpsCursor = getFuelUps(registrationNumber, date);
        if(!fuelUpsCursor.moveToNext())
        {
            //add registrationNumber and date
        }

I don't really need the _id column but it can make life tricky if tables don't have one.

Cheers, Barry

score 5 · Accepted Answer · edited May 23 '17 at 12:04

5

Your question does not make much sense. Your subject line asks for a "composite foreign key", your first sentence asks for a "composite primary key" with an AUTOINCREMENT that your sample code then ignores.

I am going to interpret your question this way: You want an _ID INTEGER PRIMARY KEY AUTOINCREMENT column in your table to be able to use Android's CursorAdapter, but you want to also make sure that the combination of two other columns is unique.

In that case, I think that you want to use a UNIQUE constraint:

edited May 23 '17 at 12:04

Community

1
1

answered Apr 02 '11 at 12:50

CommonsWare

986,068
189
2,389
2,491

Apologies - I changed my mind about primary vs foreign key after I wrote the title. I've changed it now. Thanks for your reply - UNIQUE was indeed correct - ``db.execSQL("CREATE TABLE " + FUEL_USE_TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + REGISTRATION_NO_COLUMN + " TEXT, " + DATE_TIME_COLUMN + " TEXT, UNIQUE (" + REGISTRATION_NO_COLUMN + ", " + DATE_TIME_COLUMN + "));");`` – barry Apr 02 '11 at 13:33

Android composite primary key?

1 Answers1