'How to set primary key in SQLite database after the table have been already created?

How to set primary key in SQLite after table have been already created?

I am creating a project using SQLite and I have forgetton to provide the primary key to user id,now how can I set it?

Below is my ContactContract class

public final class ContactContract {

    private ContactContract(){}


    public static class ContactEntry
    {
        public static final String TABLE_NAME="contact_info";
        public static final String KEY_P = "KEY";
        public static final String CONTACT_ID = "contact_id";
        public static final String NAME="name";
        public static final String EMAIL="email";

    }
}

Below is my ContactDbHelper class

public class ContactDbHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "contact_db";
    public static final int DATABASE_VERSION = 1;

    public static final String CREATE_TABLE="create table "+ContactContract.ContactEntry.TABLE_NAME+
            "(" + ContactContract.ContactEntry.KEY_P +"INTEGER PRIMARY KEY," +ContactContract.ContactEntry.CONTACT_ID+" number,"+ContactContract.ContactEntry.NAME+" text,"+
            ContactContract.ContactEntry.EMAIL+" text);";
android-sqliteprimary-key


Solution 1:[1]

Using :-

 public static final String CREATE_TABLE="create table "+ContactContract.ContactEntry.TABLE_NAME+
            "(" + ContactContract.ContactEntry.KEY_P +"INTEGER PRIMARY KEY," +ContactContract.ContactEntry.CONTACT_ID+" number,"+ContactContract.ContactEntry.NAME+" text,"+
            ContactContract.ContactEntry.EMAIL+" text);";

Will not work as expected because of :-

  1. You have no space after the column name and INTEGER PRIMARY KEY,,

    • the column name would be KEYINTEGER rather than KEY and would very likely result in issues.
    • the column would not be a primary key and thus would not automatically generate a useful column if a column value is omitted (as is frequently the case), the value would be null.

  2. You cant have a column named KEY (as would be the case when adding the space before INTEGER) as it's a keyword. Perhaps use public static final String KEY_P = "KEYP"; to overcome this.

As such use :-

public static final String KEY_P = "KEYP";
  • or something other than = "KEY" that suits that is not a keyword

along with :-

 public static final String CREATE_TABLE="create table "+ContactContract.ContactEntry.TABLE_NAME+
            "(" + ContactContract.ContactEntry.KEY_P +" INTEGER PRIMARY KEY," +ContactContract.ContactEntry.CONTACT_ID+" number,"+ContactContract.ContactEntry.NAME+" text,"+
            ContactContract.ContactEntry.EMAIL+" text);";

With 1 and 2 corrected. Then if you can easily regenerate the data then you could re-install the App to apply the schema changes.

However, as you state that the database has been populated and if you are unable to easily regenerate the data then it can be done.

Assuming the database currently has data (e.g. for testing the following data is part) :-

The the following method could be used to change the schema to add the PRIMARY KEY column :-

private void addPrimaryKey() {
    String TAG = "ADDPRMRYKEY";
    Log.d(TAG,"Initiated adding the primary key.");
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor csr = db.query(
            "sqlite_master",
            null,
            "name =? AND instr(sql,'PRIMARY KEY') > 0",
            new String[]{ContactContract.ContactEntry.TABLE_NAME},
            null,null,null
    );
    if (csr.getCount() < 1) {
        Log.d(TAG," PRIMARY KEY clause not found for table " + ContactContract.ContactEntry.TABLE_NAME);
        if (CREATE_TABLE.indexOf("PRIMARY KEY") > 0) {
            Log.d(TAG,"PRIMARY KEY clause located in CREATE TABLE SQL so !!!!ALTERING!!!! table " + ContactContract.ContactEntry.TABLE_NAME);
            db.execSQL("ALTER TABLE " + ContactContract.ContactEntry.TABLE_NAME + " RENAME TO OLD" + ContactContract.ContactEntry.TABLE_NAME);
            Log.d(TAG,"RENAMED TABLE " + ContactContract.ContactEntry.TABLE_NAME + " to OLD" + ContactContract.ContactEntry.TABLE_NAME);
            db.execSQL(CREATE_TABLE);
            Log.d(TAG,"CREATED new version of table " + ContactContract.ContactEntry.TABLE_NAME + " !!!!INSERTING DATA EXTRACTED!!!! from old version");
            db.execSQL("INSERT INTO " + ContactContract.ContactEntry.TABLE_NAME + " SELECT null,* FROM OLD" + ContactContract.ContactEntry.TABLE_NAME);
        } else {
            Log.d(TAG,"PRIMARY KEY clause not found in the CREATE TABLE SQL so doing nothing.");
        }
    } else {
        Log.d(TAG,"PRIMARY KEY clause found for table " + ContactContract.ContactEntry.TABLE_NAME + " - Nothing to do!!!!");
    }
    csr.close();
}
  • Note obviously the Log.d statements would be removed, they have been used for testing/explanation.

The above works by

  1. interrogating the sqlite_master table to extract the SQL used to create the table, IF it includes the PRIMARY KEY clause.

  2. If it does the method returns without doing anything as the PRIMARY KEY has already been defined.

  3. The potentially new/replacement table create SQL is checked to see if it contains the PRIMARY KEY clause.

  4. If it does not then nothing will be done.

  5. The current table is RENAMED.

  6. A new table with the original name is created according to the create table SQL.

  7. The data is copied from the original table to the new table.

    • Note that you may wish to consider deleting the renamed original table. It has not been deleted as it is safer to have it available just in case.

Rather than increase the version number the addPrimaryKey method has been incorporated into the Database Helper's constructor as per :-

public ContactDbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    addPrimaryKey(); //<<<<<<<<<< ADDED to Convert
}
  • the addPrimaryKey method will run every time but it will only attempt to convert the data if
    • the table does not have a PRIMARY KEY clause and
    • the CREATE SQL includes the PRIMARY KEY clause
    • the assumption is made that the PRIMARY KEY column is the first defined column, if not then the line db.execSQL("INSERT INTO " + ContactContract.ContactEntry.TABLE_NAME + " SELECT null,* FROM OLD" + ContactContract.ContactEntry.TABLE_NAME); should be changed accordingly.

Testing

Setup

The App was run using (KEY_P commented out as is assumed the current status):-

public static final String CREATE_TABLE = "create table " + ContactContract.ContactEntry.TABLE_NAME +
        "(" +
        //ContactContract.ContactEntry.KEY_P + " INTEGER PRIMARY KEY," + //<<<<<<<<<< comment out this line for first run to generate data
        ContactContract.ContactEntry.CONTACT_ID + " number," +
        ContactContract.ContactEntry.NAME + " text," +
        ContactContract.ContactEntry.EMAIL + " text" +
        ");";

The invoking activity used throughout was :-

public class MainActivity extends AppCompatActivity {

    ContactDbHelper mDBHlpr;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        mDBHlpr = new ContactDbHelper(this);
        addSomeData();
        Cursor csr = mDBHlpr.getWritableDatabase().query(ContactContract.ContactEntry.TABLE_NAME,null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
        csr.close();

        /*
        DataBaseHelper mDBHlpr = DataBaseHelper.getInstance(this);
        Cursor csr = mDBHlpr.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(csr);
        */
    }

    /**
     * Add some data
     */
    private void addSomeData() {
        if (DatabaseUtils.queryNumEntries(mDBHlpr.getWritableDatabase(), ContactContract.ContactEntry.TABLE_NAME) > 0 ) return;
        Random rnd = new Random();
        for (int i=0;i < 100; i++) {
            mDBHlpr.insertContact(rnd.nextInt(),"Aname" + String.valueOf(i),"Aname" + String.valueOf(i) + "@email.com");
        }
    }
}

This :-

  1. Instantiates the databasehelper
  2. Adds some data BUT only if none already exists (100 rows with randomly generated contactID's), this just to demonstrate that it works.
  3. Extract all the rows, dumping the cursor as per

:-

2019-07-04 15:52:03.813 7758-7758/aso.so56873021recopydb D/ADDPRMRYKEY: Initiated adding the primary key.
2019-07-04 15:52:03.830 7758-7758/aso.so56873021recopydb D/ADDPRMRYKEY:  PRIMARY KEY clause not found for table contact_info
2019-07-04 15:52:03.830 7758-7758/aso.so56873021recopydb D/ADDPRMRYKEY: PRIMARY KEY clause not found in the CREATE TABLE SQL so doing nothing.
2019-07-04 15:52:03.888 7758-7758/aso.so56873021recopydb I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@791f7af
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out: 0 {
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out:    contact_id=-1179778271
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out:    name=Aname0
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out:    [email protected]
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out: }
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out: 1 {
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out:    contact_id=1334348157
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out:    name=Aname1
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out:    [email protected]
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out: }
2019-07-04 15:52:03.890 7758-7758/aso.so56873021recopydb I/System.out: 2 {
2019-07-04 15:52:03.891 7758-7758/aso.so56873021recopydb I/System.out:    contact_id=1123604651
2019-07-04 15:52:03.891 7758-7758/aso.so56873021recopydb I/System.out:    name=Aname2
2019-07-04 15:52:03.891 7758-7758/aso.so56873021recopydb I/System.out:    [email protected]

Conversion

As can be seen from the output above the addPrimaryKey method has been called but it did nothing because of D/ADDPRMRYKEY: PRIMARY KEY clause not found in the CREATE TABLE SQL so doing nothing.

As such it's just a matter of changing the CREATE SQL to include the new column to be :-

public static final String CREATE_TABLE = "create table " + ContactContract.ContactEntry.TABLE_NAME +
        "(" +
        ContactContract.ContactEntry.KEY_P + " INTEGER PRIMARY KEY," + //<<<<<<<<<< comment out this line for first run to generate data
        ContactContract.ContactEntry.CONTACT_ID + " number," +
        ContactContract.ContactEntry.NAME + " text," +
        ContactContract.ContactEntry.EMAIL + " text" +
        ");";
  • i.e. the KEY_P column is no longer commented out so the new/wanted SQL is brought into play.

And the result is as per :-

2019-07-04 15:56:47.170 7979-7979/aso.so56873021recopydb D/ADDPRMRYKEY: Initiated adding the primary key.
2019-07-04 15:56:47.175 7979-7979/aso.so56873021recopydb D/ADDPRMRYKEY:  PRIMARY KEY clause not found for table contact_info
2019-07-04 15:56:47.176 7979-7979/aso.so56873021recopydb D/ADDPRMRYKEY: PRIMARY KEY clause located in CREATE TABLE SQL so !!!!ALTERING!!!! table contact_info
2019-07-04 15:56:47.176 7979-7979/aso.so56873021recopydb D/ADDPRMRYKEY: RENAMED TABLE contact_info to OLDcontact_info
2019-07-04 15:56:47.177 7979-7979/aso.so56873021recopydb D/ADDPRMRYKEY: CREATED new version of table contact_info !!!!INSERTING DATA EXTRACTED!!!! from old version
2019-07-04 15:56:47.179 7979-7979/aso.so56873021recopydb I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@791f7af
2019-07-04 15:56:47.179 7979-7979/aso.so56873021recopydb I/System.out: 0 {
2019-07-04 15:56:47.179 7979-7979/aso.so56873021recopydb I/System.out:    KEYP=1
2019-07-04 15:56:47.179 7979-7979/aso.so56873021recopydb I/System.out:    contact_id=-1179778271
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    name=Aname0
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    [email protected]
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out: }
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out: 1 {
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    KEYP=2
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    contact_id=1334348157
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    name=Aname1
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    [email protected]
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out: }
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out: 2 {
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    KEYP=3
2019-07-04 15:56:47.180 7979-7979/aso.so56873021recopydb I/System.out:    contact_id=1123604651
2019-07-04 15:56:47.181 7979-7979/aso.so56873021recopydb I/System.out:    name=Aname2
2019-07-04 15:56:47.181 7979-7979/aso.so56873021recopydb I/System.out:    [email protected]

i.e. the data has been retained but the new column has been introduced and values assigned.

Solution 2:[2]

In SQLite there is no option to add the primary key for the existing table. So if you want to add the primary key to the existing table follow the below steps.

  1. Create table with columns and mark the column as the primary key. For fresh installs of app, inside onCreate() method,

    CREATE TABLE cities (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL

    );

  2. For existing db, in onUpgrade() method

     BEGIN TRANSACTION; //db.beginTransaction();

 ALTER TABLE cities RENAME TO old_cities;

 CREATE TABLE cities (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
 );

 INSERT INTO cities SELECT * FROM old_cities;

 DROP TABLE old_cities;

 COMMIT; //db.setTransactionSuccessful();

  3. Code it inside try/catch, have finally block to end the transaction.

     finally {
  db.endTransaction();
 }

  4. Don't forget to increment the database version.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Dharman

Related Questions

Using text as a primary key in SQLite table bad?

MS Access ignores trailing space(s) in primary key validation

Dynamodb will consider the secondary index also a primary key, before put item into table.?

Difference between non-prime and non-key attributes in Database

Update on Duplicate key with two columns to check mysql

AUTOINCREMENT primary key for snowflake bulk loading

SQL: How to generate a unique value for a Primary Key

Duplicate Primary Key while doing latteral flattening in SNOWFLAKE

FHIR works on AWS server not allowing to keep customized id as primary key

Hibernate : functionnally calling hibernate to recreate constraints

Is it possible to use Paging (As part of android jetpack) without using Room DB?

How do catch error on React Native SQLite

Android error getSlotFromBufferLocked: unknown buffer: 0xa9fb8d90

android-How to protect a file from copying or seeing by users

Copy Data from CSV file to Sqlite file in Android