CODEWITHSUNDEEP
javaandroidsqlite
thomaspane
thomaspane

Reputation: 35

SQLite error inserting constraint field - why?

I am new to android programming and using SQLite. I am trying to insert into a database called "menu" which has a table called "items". The attributes of this table are "name" and "price" which are text and real respectively. (I would like to apologize for all the code, but I figure these will be useful). My "MySQLiteHelper" class creates the table as such:

package com.example.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

    //Creating the items table
    public  static final String TABLE_ITEMS = "items";
    public  static final String ITEMS_COLUMN_NAME = "name";
    public  static final String ITEMS_PRICE = "price";



    private static final String DATABASE_NAME  = "menu";
    private static final int DATABASE_VERSION  = 1;

    // create table comments(
    //   _id integer primary key autoincrement,
    //   comment text not null );
    private static final String TABLE_CREATE_ITEMS = "create table "
            + TABLE_ITEMS + "( " + ITEMS_COLUMN_NAME + " text primary key, "
            + ITEMS_PRICE + " real not null );";

    public MySQLiteHelper( Context context ) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION );
    }

    @Override
    public void onCreate( SQLiteDatabase database ) {
        //Create the items table
        database.execSQL( TABLE_CREATE_ITEMS );
        //Create the combos table
        //database.execSQL(TABLE_CREATE_COMBO);
    }

    @Override
    public void onUpgrade( SQLiteDatabase db, int oldVersion, int newVersion ) {
        Log.w( MySQLiteHelper.class.getName( ), "Upgrading database from version "
                + oldVersion + " to " + newVersion + ", which will destroy all old data" );
        db.execSQL( "DROP TABLE IF EXISTS" + TABLE_CREATE_ITEMS );
        //db.execSQL( "DROP TABLE IF EXISTS" + TABLE_CREATE_COMBO );
        onCreate( db );
    }
}

I can confirm that the database "menu" is created within the data/data/package/databases folder. Although I am not able to connect to SQLite using adb shell so I cannot confirm table schema. Upon clicking the insert button my datasource.createComment(String, Float) gets called:

public void onClick (View v) {
    @SuppressWarnings("unchecked")
    //Load up the current values in the adapter into this adapter object
            ArrayAdapter<Comment> adapter = (ArrayAdapter<Comment>)getListAdapter();
    //Set comment to null
    Comment comment = null;
    //Check to see which button was pressed
    switch (v.getId()) {
        case R.id.insert:
            String[] option = new String[] {"One", "Two", "Three"};
            int index = new Random().nextInt(3);
            //When inserting comment you just have to pass value
            comment = datasource.createComment("TEST", (float) 12.30);
            adapter.add(comment); //Adding to listview
            break;
        case R.id.delete:
            if(getListAdapter().getCount() > 0) {
                comment = (Comment)getListAdapter().getItem(0);
                adapter.remove(comment);//removing from listview
                datasource.deleteComment(comment); //delete from db.
            }
            break;
    }
    //Updating the adapter
    adapter.notifyDataSetChanged();
}

Through some debugging (print statement) I can confirm that database.insert is returning -1. It is not successfuly inserting. The following error gets printed out in my Logcat

12-16 23:42:30.002 11063-11063/com.example.sqlite E/SQLiteDatabase: Error inserting price=12.3 name=TEST
    android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
        at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1718)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1591)
        at com.example.sqlite.CommentsDataSource.createComment(CommentsDataSource.java:47)
        at com.example.sqlite.InsertItems.onClick(InsertItems.java:50)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at android.view.View$1.onClick(View.java:3039)
        at android.view.View.performClick(View.java:3511)
        at android.view.View$PerformClick.run(View.java:14105)
        at android.os.Handler.handleCallback(Handler.java:605)
        at android.os.Handler.dispatchMessage(Handler.java:92)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4424)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
        at dalvik.system.NativeStart.main(Native Method)

The code for my "CommentsDataSource" is below. Please ignore the methods after createComment(). I am editing my professors code one method at a time. 

public class CommentsDataSource {

    // Database fields
    //Used toe xecute commands on db.
    private SQLiteDatabase database;
    //Opening up the mysqlhelper which is in charge of opening up connection and maintaining
    private MySQLiteHelper dbHelper;
    //Holds all the values from the database
    private String[] allColumnsItems = { MySQLiteHelper.ITEMS_COLUMN_NAME,
            MySQLiteHelper.ITEMS_PRICE };

    //Creating the default constructor
    public CommentsDataSource(Context context) {
        //Give context to the class that manages the connection
        dbHelper = new MySQLiteHelper(context);
    }

    //Opening up the connction to the db
    public void open() throws SQLException {
        //Open the db for reading and writing
        database = dbHelper.getWritableDatabase(); //getWritableDatabase is what opens the connection
    }

    //Closing the database
    public void close() throws SQLException {
        dbHelper.close();
    }

    //Creating and commiting comment
    public Comment createComment(String comment, float price) {
        //Insert into comments values('')
        ContentValues values = new ContentValues();
        //Load the column id and value into ContentValues
        values.put(MySQLiteHelper.ITEMS_COLUMN_NAME, comment);
        values.put("price", price);
        //Now insert into table
        long insertID = database.insert(MySQLiteHelper.TABLE_ITEMS, null, values);
        if(insertID == -1) {
            System.out.println("JSDLFJLSJDFL");
        }
        //Create a cursor to keep track of results. When creating cursors they equal database.query
        Cursor cursor =  database.query(MySQLiteHelper.TABLE_ITEMS, allColumnsItems, null, null, null, null, null);
        //Move cursor tot he front
        cursor.moveToFirst();
        //Return the comment which is added to listview
        return cursorToComment(cursor);
    }

    public void deleteComment(Comment comment) {
        // select id, comment from comments;
        long id = comment.getId();
        //Debug purposes
        System.out.println("Comment to be deleted: " + id);
        //Deleting from db
        database.delete(MySQLiteHelper.TABLE_ITEMS, MySQLiteHelper.ITEMS_COLUMN_NAME + "=" + comment, null);
    }

    public List<Comment> getAllComments() {
        //Select * from comments
        List<Comment> comments = new ArrayList<Comment>();
        //Fetching the results of query into cursor
        Cursor cursor = database.query(MySQLiteHelper.TABLE_ITEMS, allColumnsItems, null, null, null, null, null);
        //Move to front of cursor
        cursor.moveToFirst();
        //Iterate through cursor
        while(!cursor.isAfterLast()) {
            Comment comment = cursorToComment(cursor);
            //Add results to arraylist
            comments.add(comment);
            cursor.moveToNext();

        }
        cursor.close();
        return comments;
    }

    public Comment cursorToComment(Cursor cursor) {
        Comment comment = new Comment();
        comment.setId(cursor.getLong(0));
        comment.setComment(cursor.getString(1));
        return comment;
    }

}

I am struggling to see where my mistake is. My constraints should all be valid?

Upvotes: 1

Views: 327

Answers (3)

Shashanth
Shashanth

Reputation: 5180

After going through your code, I can assume that you're trying to have unique item name in a table right? If it's the case, then that error is obvious. In your table items, you've name as primary key and you're trying to insert TEST each time. Which is already exists inside the table.

There're 3 ways to solve this problem.

  1. Remove primary key constraint from name column. But, you can enter duplicate names after that.

  2. Don't hard code values while testing. Generate unique name and insert it. Like below

    int index = new Random().nextInt(3);
comment = datasource.createComment("TEST" + index, (float) 12.30);

// concatenate `index` variable to `TEST` that's it.

  3. Take input from user (GUI) and insert it to the table.

PS: The method nextInt(3) will return numbers from 0 to 2. When you test your code, you get duplicate names. So, beware while using random number generator. I'd like recommend my last point for this.

Upvotes: 1

Rubick
Rubick

Reputation: 306

Please try this:

public void onClick (View v) {
@SuppressWarnings("unchecked")
    //Load up the current values in the adapter into this adapter object
            ArrayAdapter<Comment> adapter = (ArrayAdapter<Comment>)getListAdapter();
    //Set comment to null
    Comment comment = new Comment(); //have to change this from null to new Comment();
    //Check to see which button was pressed
    switch (v.getId()) {
        case R.id.insert:
            String[] option = new String[] {"One", "Two", "Three"};
            int index = new Random().nextInt(3);
            //When inserting comment you just have to pass value
            comment = datasource.createComment("TEST", (float) 12.30);
            adapter.add(comment); //Adding to listview
            break;
        case R.id.delete:
            if(getListAdapter().getCount() > 0) {
                comment = (Comment)getListAdapter().getItem(0);
                adapter.remove(comment);//removing from listview
                datasource.deleteComment(comment); //delete from db.
            }
            break;
    }
    //Updating the adapter
    adapter.notifyDataSetChanged();
}

Also your SQLiteOpenHelper

public class MySQLiteHelper extends SQLiteOpenHelper {

    //Creating the items table
    public  static final String TABLE_ITEMS = "items";
    public  static final String ITEMS_COLUMN_NAME = "name";
    public  static final String ITEMS_PRICE = "price";



    private static final String DATABASE_NAME  = "menu";
    private static final int DATABASE_VERSION  = 1;

    // create table comments(
    //   _id integer primary key autoincrement,
    //   comment text not null );
     private static final String TABLE_CREATE_ITEMS = "CREATE TABLE IF NOT EXISTS " + TABLE_ITEMS + "("
            + ITEMS_AUTO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //this is just _id
            + ITEMS_COLUMN_NAME + " VARCHAR,"
            + ITEMS_PRICE + " FLOAT)";

    public MySQLiteHelper( Context context ) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION );
    }

    @Override
    public void onCreate( SQLiteDatabase database ) {
        //Create the items table
        database.execSQL( TABLE_CREATE_ITEMS );
        //Create the combos table
        //database.execSQL(TABLE_CREATE_COMBO);
    }

    @Override
    public void onUpgrade( SQLiteDatabase db, int oldVersion, int newVersion ) {
        Log.w( MySQLiteHelper.class.getName( ), "Upgrading database from version "
                + oldVersion + " to " + newVersion + ", which will destroy all old data" );
        db.execSQL( "DROP TABLE IF EXISTS" + TABLE_CREATE_ITEMS );
        //db.execSQL( "DROP TABLE IF EXISTS" + TABLE_CREATE_COMBO );
        onCreate( db );
    }
}

Upvotes: 0

Neha Jobanpurtra
Neha Jobanpurtra

Reputation: 1

Here you are trying to creating table with text primary key which ideally should be auto increment. That is why its returning constrain failed.

 private static final String TABLE_CREATE_ITEMS = "create table "
        + TABLE_ITEMS + "( " + ITEMS_COLUMN_NAME + " text primary key, "
        + ITEMS_PRICE + " real not null );";

It should be 

CREATE TABLE TableName(
_id     INTEGER PRIMARY KEY,      -- autoincrementing, for Android
NAME    TEXT,
[...]);

You may refer Inserting values to SQLite table in Android

Upvotes: 0

Related Questions