sqlite – Is it possible to apply primary key on the text fields in android database-ThrowExceptions

Exception or error:

I have created a simple table that contains the name and email id’s of the persons. When i am giving the create query like this:

"create table contacts (name text not null, email text primary key not null);"

But this is not working. I’m not getting any exception or error while running nor the primary key is working. While browsing the table using SQLite browser i observed that there are only four data types:

enter image description here

Is it possible to apply primary key only on integer? What is the case if i want primary key on text fields. Thank you. This is the code that i used:

public class DBAdapter {

public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_EMAIL = "email";
private static final String TAG = "DBAdapter";

private static final String DATABASE_NAME = "MyDB";
private static final String DATABASE_TABLE = "contacts";
private static final int DATABASE_VERSION = 2;

private static final String DATABASE_CREATE =
    "create table contacts (_id integer primary key autoincrement, "
    + "name text not null, email text not null);";

private final Context context;    

private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DBAdapter(Context ctx) 
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);

private static class DatabaseHelper extends SQLiteOpenHelper 
    DatabaseHelper(Context context) 
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    public void onCreate(SQLiteDatabase db) 
        try {
        } catch (SQLException e) {

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS contacts");

//---opens the database---
public DBAdapter open() throws SQLException 
    db = DBHelper.getWritableDatabase();
    return this;

//---closes the database---    
public void close() 

//---insert a contact into the database---
public long insertContact(String name, String email) 
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, name);
    initialValues.put(KEY_EMAIL, email);
    return db.insert(DATABASE_TABLE, null, initialValues);

//---deletes a particular contact---
public boolean deleteContact(long rowId) 
    return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;

//---retrieves all the contacts---
public Cursor getAllContacts() 
    return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
            KEY_EMAIL}, null, null, null, null, null);

//---retrieves a particular contact---
public Cursor getContact(long rowId) throws SQLException 
    Cursor mCursor =
            db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
            KEY_NAME, KEY_EMAIL}, KEY_ROWID + "=" + rowId, null,
            null, null, null, null);
    if (mCursor != null) {
    return mCursor;

//---updates a contact---
public boolean updateContact(long rowId, String name, String email) 
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, name);
    args.put(KEY_EMAIL, email);
    return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
How to solve:

as per the faq of sqlite documentation, using TEXT as a datatype for primary key should work.

i used your query and here it is, the table is created.

CREATE TABLE contacts ( email text primary key not null, name text not null);

INSERT INTO contacts VALUES ('sample@email.com', 'sample')
INSERT INTO contacts VALUES ('people@email.com', 'sample')

enter image description here

enter image description here

now here is where it went wrong.

when i ran this again

INSERT INTO contacts VALUES ('sample@email.com', 'sample')

nothing happened, no errors.
But it did not update the record. so i conclude data integrity is there but you don’t get any feedback about the failure.


SQLiteDatabase.insertOrThrow() should throw an exception if the value could not be inserted.
Also its return values, as well the one from SQLiteDatabase.insert() is the rowid of the inserted row or -1 if it fails. You should opt for any of these alternatives in your code.

Leave a Reply

Your email address will not be published. Required fields are marked *