Ошибка primary key must be unique

This is my first attempt at implementing SQLite and I am running into a common error that I have not been able to resolve. The error is

03-12 15:37:20.432: E/SQLiteDatabase(1603): Error inserting Test=test string value Entry=entry string value _id=1
03-12 15:37:20.432: E/SQLiteDatabase(1603): android.database.sqlite.SQLiteConstraintException: PRIMARY KEY must be unique (code 19)

I have a high level understanding of what might be going on. The error is saying that my primary key—which is _id for me—should be unique for each entry. But as far as I know, I am only trying to add one element, and I believe I have set things up based on the tutorials I have followed. Please take a look at what I have, and let me know what I have done in error. Thanks in advance!

First, I try to save an entry with the below line. I set id as 1 and then try to save some test values in other columns

DatabaseManager.saveEntry(MainActivity.this, "1", "entry string value", "test string value");

DatabaseManager.java

public class DatabaseManager {

    public static void saveEntry(Context context, String id, String entry, String test) {

        try {
            ContentValues values = getContentValuesEntryTable(id, entry, test);
            ContentResolver resolver = context.getContentResolver();
            Cursor cursor = resolver.query(EntryTable.CONTENT_URI, null, null, null, null);
            if (cursor != null && cursor.getCount() > 0) {
                resolver.update(EntryTable.CONTENT_URI, values, null, null);
            } else {
                resolver.insert(EntryTable.CONTENT_URI, values);
            }
            cursor.close();
            resolver.insert(EntryTable.CONTENT_URI, values);
        } catch (Exception e) {
            Log.e("TEST", "error: " + e.toString());
            e.printStackTrace();
        }

    }

    public static Cursor getEntry(Context context, String entry) {
        Cursor cursor;
        String sorting = null;
        if (TextUtils.isEmpty(entry)) {
            cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, null, null, sorting);
        } else {
            cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, EntryTable.Cols.COLUMN_ENTRY + " ='" + entry + "'", null, sorting);
        }

        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    public static Cursor getTest(Context context, String test) {
        Cursor cursor;
        String sorting = null;
        if (TextUtils.isEmpty(test)) {
            cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, null, null, sorting);
        } else {
            cursor = context.getContentResolver().query(EntryTable.CONTENT_URI, null, EntryTable.Cols.COLUMN_TEST + " = '" + test + "'", null, sorting);
        }

        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    private static ContentValues getContentValuesEntryTable(String id, String entry, String test) {
        ContentValues values = new ContentValues();
        values.put(EntryTable.Cols.COLUMN_ID, id);
        values.put(EntryTable.Cols.COLUMN_ENTRY, entry);
        values.put(EntryTable.Cols.COLUMN_TEST, test);
        return values;
    }
}

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String KEY_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS {0} ({1})";
    public static final String KEY_DROP_TABLE = "DROP TABLE IF EXISTS {0}";

    private static final int CURRENT_DB_VERSION = 1;
    private static final String DB_NAME = "qmun.db";

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, CURRENT_DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("TEST", "DB Creation :: Going to create db ");
        createEntryTable(db);
    }

    private void createEntryTable(SQLiteDatabase db) {
        StringBuilder entryTableFields = new StringBuilder();
        entryTableFields.append(EntryTable.Cols.COLUMN_ID)
        .append(" INTEGER PRIMARY KEY AUTOINCREMENT, ")
        .append(EntryTable.Cols.COLUMN_ENTRY).append(" TEXT, ")
        .append(EntryTable.Cols.COLUMN_TEST).append(" TEXT");
        createTable(db, EntryTable.TABLE_NAME, entryTableFields.toString());
    }

    public void dropTable(SQLiteDatabase db, String name) {
        String query = MessageFormat.format(DatabaseHelper.KEY_DROP_TABLE, name);
        db.execSQL(query);
    }

    public void createTable(SQLiteDatabase db, String name, String fields) {
        String query = MessageFormat.format(DatabaseHelper.KEY_CREATE_TABLE, name, fields);
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        dropTable(db, EntryTable.TABLE_NAME);
        onCreate(db);
    }

}

DatabaseProvider.java

public class DatabaseProvider extends ContentProvider {

    private static final String UNKNOWN_URI = "unknown_uri";
    private DatabaseHelper dBHelper;

    @Override
    public boolean onCreate() {
        // TODO Auto-generated method stub
        Log.i("TEST", "creating db");
        dBHelper = new DatabaseHelper(getContext());
        dBHelper.getWritableDatabase();
        return false;
    }

    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
        // TODO Auto-generated method stub
        SQLiteDatabase db = dBHelper.getReadableDatabase();
        final int token = ContentDescriptor.URI_MATCHER.match(uri);
        Cursor result = null;

        switch (token) {
        case EntryTable.PATH_TOKEN: {
            result = doQuery(db, uri, EntryTable.TABLE_NAME, projection, selection, selectionArgs,
                sortOrder);
            break;
        }
        default:
            break;
        }

        // db.close(); // added line, investigate if needed. Received lint about it
        return result;
    }

    private Cursor doQuery(SQLiteDatabase db, Uri uri, String tableName, String[] projection,
        String selection, String[] selectionArgs, String sortOrder) {

        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(tableName);
        Cursor result = builder.query(db, projection, selection, selectionArgs, sortOrder, null, null);
        result.setNotificationUri(getContext().getContentResolver(), uri);
        return result;
    }

    @Override
    public String getType(Uri uri) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Uri insert(Uri uri, ContentValues values) {
        // TODO Auto-generated method stub
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        int token = ContentDescriptor.URI_MATCHER.match(uri);
        Uri result = null;

        switch (token) {
        case EntryTable.PATH_TOKEN: {
            result = doInsert(db, EntryTable.TABLE_NAME, EntryTable.CONTENT_URI, uri, values);
            break;
        }
        default:
            break;
        }

        db.close(); // added line, investigate if needed. Received lint about it

        if (result == null) {
            throw new IllegalArgumentException(UNKNOWN_URI + uri);
        }

        return result;
    }

    @Override
    public int bulkInsert(Uri uri, ContentValues[] values) {
        String table = null;
        int token = ContentDescriptor.URI_MATCHER.match(uri);

        switch (token) {
        case EntryTable.PATH_TOKEN: {
            table = EntryTable.TABLE_NAME;
            break;
        }
        default:
            break;
        }

        SQLiteDatabase db = dBHelper.getWritableDatabase();
        db.beginTransaction();

        for (ContentValues cv : values) {
            db.insert(table, null, cv);
        }

        db.setTransactionSuccessful();
        db.endTransaction();
        db.close(); // added line, investigate if needed. Received lint about it

        return values.length;
    }

    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {
        // TODO Auto-generated method stub

        SQLiteDatabase db = dBHelper.getWritableDatabase();
        int token = ContentDescriptor.URI_MATCHER.match(uri);
        int result = 0;

        switch (token) {
        case EntryTable.PATH_TOKEN: {
            result = doDelete(db, uri, EntryTable.TABLE_NAME, selection, selectionArgs);
            break;
        }
        default:
            break;
        }

        db.close(); // added line, investigate if needed. Received lint about it
        return result;
    }

    @Override
    public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
        // TODO Auto-generated method stub
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        int token = ContentDescriptor.URI_MATCHER.match(uri);
        int result = 0;

        switch (token) {
        case EntryTable.PATH_TOKEN: {
            result = doUpdate(db, uri, EntryTable.TABLE_NAME, selection, selectionArgs, values);
            break;
        }
        default:
            break;
        }

        db.close(); // added line, investigate if needed. Received lint about it
        return result;
    }

    private int doUpdate(SQLiteDatabase db, Uri uri, String tableName, String selection,
        String[] selectionArgs, ContentValues values) {
        int result = db.update(tableName, values, selection, selectionArgs);
        getContext().getContentResolver().notifyChange(uri, null);
        return result;
    }

    private int doDelete(SQLiteDatabase db, Uri uri, String tableName, String selection,
        String[] selectionArgs) {
        int result = db.delete(tableName, selection, selectionArgs);
        getContext().getContentResolver().notifyChange(uri, null);
        return result;
    }

    private Uri doInsert(SQLiteDatabase db, String tableName, Uri contentUri, Uri uri, ContentValues values) {
        long id = db.insert(tableName, null, values);
        Uri result = contentUri.buildUpon().appendPath(String.valueOf(id)).build();
        getContext().getContentResolver().notifyChange(uri, null);
        return result;
    }

}

DBHelper.java

public class DBHelper extends SQLiteOpenHelper {
    public static String TAG = DBHelper.class.getSimpleName();

    // Constants
    private static Context mContext;
    private SQLiteDatabase mDatabase;
    public static final String KEY_ID = "id";

    private static final String DATABASE_NAME = "abcmouse.db";
    private static final String DATABASE_PATH = "data/sqlite/abcmouse/";
    private static final String DATABASE_TABLE = "tbl_masteraccount";
    private static final int DATABASE_VERSION = 1;

    public DBHelper(Context context) {
        // TODO Auto-generated constructor stub
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = context;

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        System.out.println("Upgrading db version (v" + oldVersion +
            ") to (v" + newVersion + ")");
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);

    }

    /**
     * Method is used to create an empty database that will be created into the default system path
     * of the application. The former database will be overwritten
     *
     * @throws IOException
     */
    public void createDatabase() throws IOException {
        boolean dBExist = checkDatabase();
        if (!dBExist) {
            getReadableDatabase();
            try {
                copyDatabase();
            } catch (Exception e) {
                Log.e(TAG, "error creating db: " + e.toString());
                e.printStackTrace();
            }
        }
    }

    /**
     * Method is used to copy database from local assets folder to the created database in the
     * system folder. The copying is done via transferring bytestream
     *
     * @throws IOException
     */
    private void copyDatabase() throws IOException {
        // open local db as input stream
        InputStream is = mContext.getAssets().open(DATABASE_NAME);

        // path to the new created empty db
        String outFileName = DATABASE_PATH + DATABASE_NAME;

        // open the empty db as the output stream
        OutputStream os = new FileOutputStream(outFileName);

        // transfer bytes from the inputfile to the outputfile
        byte buffer[] = new byte[1024];

        int length;
        while ((length = is.read(buffer)) > 0) {
            os.write(buffer, 0, length);
        }

        // close streams
        os.flush();
        os.close();
        is.close();
    }

    /**
     * Method is used to check if the database already exists to avoid re-copying the file each time
     * you open the application
     *
     * @return true if it exists, otherwise false
     */
    private boolean checkDatabase() {
        SQLiteDatabase checkDb = null;
        try {
            String mPath = DATABASE_PATH + DATABASE_NAME;
            checkDb = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READONLY);
        } catch (Exception e) {
            Log.e(TAG, "error checking existing db: " + e.toString());
            e.printStackTrace();
        }

        if (checkDb != null) {
            checkDb.close();
        }
        return checkDb != null ? true : false;
    }

    /**
     * Method is used to add data to database
     *
     * @param table
     * @param key
     * @param value
     * @return
     */
    public long addData(String table, String[] key, String[] value) {
        mDatabase = getWritableDatabase();
        if (mDatabase.isOpen()) {
            ContentValues cv = new ContentValues();
            for (int i = 0; i < key.length; i++) {
                cv.put(key[i], value[i]);
            }
            return mDatabase.insert(table, null, cv);
        }
        return 0;
    }

    /**
     * Method is used to update data on database
     *
     * @param table
     * @param key
     * @param value
     * @param whereClause
     * @return
     */
    public long upgradeData(String table, String[] key, String[] value, String whereClause) {
        mDatabase = getWritableDatabase();
        if (mDatabase.isOpen()) {
            ContentValues cv = new ContentValues();
            for (int i = 0; i < key.length; i++) {
                cv.put(key[i], value[i]);
            }
            return mDatabase.update(table, cv, whereClause, null);
        }
        return 0;
    }

    /**
     * Method is used to retrieve stored data
     *
     * @param table
     * @param columns
     * @param selection
     * @param selectionArgs
     * @param groupBy
     * @param having
     * @param orderBy
     * @return
     */
    public Cursor getAllData(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
        Cursor cursor = null;
        if (mDatabase != null) {
            if (!mDatabase.isOpen()) {
                mDatabase = getWritableDatabase();
            }

            if (mDatabase.isOpen()) {
                cursor = mDatabase.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
            }
        }
        return cursor;
    }

    /**
     * Method is used to open the database
     *
     * @throws SQLException
     */
    public void openDatabase() throws SQLException {
        String mPath = DATABASE_PATH + DATABASE_NAME;
        mDatabase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READONLY);
    }

    public long deleteRecord(String table, String condition) {
        mDatabase = getWritableDatabase();
        if (mDatabase.isOpen()) {
            return mDatabase.delete(table, condition, null);
        }

        return 0;
    }

    @Override
    public void close() {
        if (mDatabase != null) {
            mDatabase.close();
        }
        super.close();
    }

}

In my manifest I have provider set

<provider
            android:name=".helper.DatabaseProvider"
            android:authorities="com.abcmouse"
            android:exported="true"
            android:grantUriPermissions="true"
            android:readPermission="com.abcmouse.READ"
            android:writePermission="com.abcmouse.WRITE" />

private void tsbtnEdit_Click(object sender, EventArgs e)
        {
            try
            {
                frmAddEditEvents obj = new frmAddEditEvents();
                obj.ShowDialog();
                    SQLiteConnection connection = new SQLiteConnection(connectionString);
                    if (grdEvents.Rows.Count > 1 && grdEvents.SelectedRows[0].Index != grdEvents.Rows.Count - 0)
                    {
                        delcmd.CommandText = "UPDATE AddEvent SET ID=" + grdEvents.SelectedRows[0].Cells[0].Value.ToString()+ "";
                        connection.Open();
                        delcmd.Connection = connection;
                        delcmd.ExecuteNonQuery();
                        connection.Close();  

                        grdEvents.Update();
                        MessageBox.Show("Row updated");
                    }
                else
                {
                    MessageBox.Show("Please select any record to update");
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

delcmd.CommandText = "UPDATE AddEvent SET ID=" + grdEvents.SelectedRows[0].Cells[0].Value.ToString()+ "";

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900

I am combining various db (sqlite ) there is a chance from time to time that they may have the same incident number (unique Identifier) how can I deal with this in my DB(sqlite) ? or python coding ?

Traceback (most recent call last):
  File "C:Python26signgetting_it_work.py", line 22, in <module>
    Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp))
sqlite3.IntegrityError: PRIMARY KEY must be unique

Open in new window

import csv
import sqlite3 as lite

fname="recieved_data.txt"
f = open(fname, 'rb')
print f
# notice the binary mode again
reader = csv.reader(f)
for row in reader:    
    (Incident_number,Last_Name,First_Name,Age,Gender,Address,City,State,Zip,Ailment,Treatment,Patient_reprt,Initial_contact,Hospital, 
    Destination,Inservice,Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather,Temp,Humid,Wind,TimeStamp) =row
    print row
    #print name
    Age = int(Age)
    Incident_number=int(Incident_number)	# convert to a number
	
    con = lite.connect('specialeventms.sqlite')
    cur = con.cursor()
    cur.execute('insert into ptrecords values(?,?,?,?,?,?,?,?,?,?,?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)',
                (Incident_number, Last_Name, First_Name, Age,Gender,Address,City,State,Zip
                 ,Ailment,Treatment,Patient_reprt,Initial_contact,Hospital,Destination,Inservice,
                 Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp))
    #cur.execute('insert into neighbours values(?, ?, ?)', (name, age, remark))
    con.commit()
    cur.close()
    con.close()
    
f.close()

Open in new window

Tag1 supports the Drupal Project.

return array_search($args, NULL) === FALSE ? max($args) : NULL;

return array_search($args, NULL, TRUE) === FALSE ? max($args) : NULL;

return !in_array(NULL, $args, TRUE) ? max($args) : NULL;

update sequences set value=123;