James James - 7 days ago 6
Java Question

UNIQUE constraint failed , SQLite, Java

I have a database with methods addPub and GetAllPubs:

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;


public class Database extends SQLiteOpenHelper{
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "Pubs.db";
public static final String DATABASE_TABLE = "pubsTable";
public static final String COL_1 = "ID";
public static final String COL_2 = "NAME";
public static final String COL_3 = "LAT";
public static final String COL_4 = "LNG";
public static final String COL_5 = "OPEN";
public static final String COL_6 = "CLOSE";
public static final String COL_7 = "PUB";
public static final String COL_8 = "COCKTAIL";
public static final String COL_9 = "BEER";
public static final String COL_10 = "FOOD";
public static final String COL_11 = "DART";
public static final String COL_12 = "POOL";
public static final String COL_13 = "MUSIC";
public static final String COL_14 = "SPORT";
public static final String COL_15 = "OUTSIDE";
public static final String COL_16 = "PAY";
public static final String COL_17 = "QUIZ";
public static final String COL_18 = "PHONE";


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

@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = "CREATE TABLE" + " " +DATABASE_TABLE + "("
+ COL_1 +" " +"INTEGER PRIMARY KEY AUTOINCREMENT,"
+ COL_2 +" " + "TEXT,"
+ COL_3 +" " + "TEXT,"
+ COL_4 +" " + "TEXT,"
+ COL_5 +" " + "TEXT,"
+ COL_6 +" " + "TEXT,"
+ COL_7 +" " + "TEXT,"
+ COL_8 +" " + "TEXT,"
+ COL_9 +" " + "TEXT,"
+ COL_10 +" " + "TEXT,"
+ COL_11 +" " + "TEXT,"
+ COL_12 +" " + "TEXT,"
+ COL_13 +" " + "TEXT,"
+ COL_14 +" " + "TEXT,"
+ COL_15 +" " + "TEXT,"
+ COL_16 +" " + "TEXT,"
+ COL_17 +" " + "TEXT,"
+ COL_18 +" " + "TEXT"
+ ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
onCreate(db);
}

void addPub(Pub pub) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COL_1, pub.getID());
values.put(COL_2, pub.getName());
values.put(COL_3, pub.getLat());
values.put(COL_4, pub.getLng());
values.put(COL_5, pub.getOpen());
values.put(COL_6, pub.getClose());
values.put(COL_7, pub.getPub());
values.put(COL_8, pub.getCocktail());
values.put(COL_9, pub.getBeer());
values.put(COL_10, pub.getFood());
values.put(COL_11, pub.getDart());
values.put(COL_12, pub.getPool());
values.put(COL_13, pub.getMusic());
values.put(COL_14,pub.getSport());
values.put(COL_15, pub.getOutside());
values.put(COL_16, pub.getPay());
values.put(COL_17, pub.getQuiz());
values.put(COL_18, pub.getPhone_number());

db.insert(DATABASE_TABLE, null, values);
db.close();
}

public List<Pub> getAllPubs() {
List<Pub> pubList = new ArrayList<>();
String selectQuery = "SELECT * FROM " + DATABASE_TABLE;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
Pub pub = new Pub();
pub.setID(Integer.parseInt(cursor.getString(0)));
pub.setName(cursor.getString(1));
pub.setLat(cursor.getString(2));
pub.setLng(cursor.getString(3));
pub.setOpen(cursor.getString(4));
pub.setClose(cursor.getString(5));
pub.setPub(cursor.getString(6));
pub.setCocktail(cursor.getString(7));
pub.setBeer(cursor.getString(8));
pub.setFood(cursor.getString(9));
pub.setDart(cursor.getString(10));
pub.setPool(cursor.getString(11));
pub.setMusic(cursor.getString(12));
pub.setSport(cursor.getString(13));
pub.setOutside(cursor.getString(14));
pub.setPay(cursor.getString(15));
pub.setQuiz(cursor.getString(16));
pub.setPhone_number(cursor.getString(17));
pubList.add(pub);
} while (cursor.moveToNext());
}
return pubList;
}


}

And Class Pub:

public class Pub {
int _id;
String _name;
String _lat;
String _lng;
String _open;
String _close;
String _pub;
String _cocktail;
String _beer;
String _food;
String _dart;
String _pool;
String _music;
String _sport;
String _outside;
String _pay;
String _quiz;
String _phone_number;

public Pub() {
}

public Pub(int id, String name, String lat, String lng, String open, String close, String pub, String cocktail, String beer, String food, String dart, String pool, String music,String sport, String outside, String pay, String quiz, String phone_number){
this._id = id;
this._name = name;
this._lat = lat;
this._lng = lng;
this._open= open;
this._close=close;
this._pub=pub;
this._cocktail=cocktail;
this._beer=beer;
this._food=food;
this._dart=dart;
this._pool=pool;
this._music=music;
this._sport=sport;
this._outside=outside;
this._pay=pay;
this._quiz=quiz;
this._phone_number=phone_number;
}
public int getID(){
return this._id;
}
public String getName(){
return this._name;
}
public String getLat(){
return this._lat;
}
public String getLng(){
return this._lng;
}
public String getOpen(){
return this._open;
}
public String getClose(){
return this._close;
}
public String getPub(){
return this._pub;
}
public String getCocktail(){
return this._cocktail;
}
public String getBeer(){
return this._beer;
}
public String getFood(){
return this._food;
}
public String getDart(){
return this._dart;
}
public String getPool(){
return this._pool;
}
public String getMusic(){
return this._music;
}
public String getSport(){
return this._sport;
}
public String getOutside(){
return this._outside;
}
public String getPay(){
return this._pay;
}
public String getQuiz(){
return this._quiz;
}
public String getPhone_number(){
return this._phone_number;
}

public void setID(int id){
this._id = id;
}
public void setName(String name){
this._name = name;
}
public void setLat(String lat){
this._lat = lat;
}
public void setLng(String lng){
this._lng = lng;
}
public void setOpen(String open){
this._open = open;
}
public void setClose(String close){
this._close = close;
}
public void setPub(String pub){
this._pub = pub;
}
public void setCocktail(String cocktail){
this._cocktail = cocktail;
}
public void setBeer(String beer){
this._beer = beer;
}
public void setFood(String food){
this._food = food;
}
public void setDart(String dart){
this._dart = dart;
}
public void setPool(String pool){
this._pool = pool;
}
public void setMusic(String music){this._music = music; }
public void setSport(String sport){
this._sport = sport;
}
public void setOutside(String outside){
this._outside = outside;
}
public void setPay(String pay){
this._pay = pay;
}
public void setQuiz(String quiz){
this._quiz = quiz;
}
public void setPhone_number(String phone_number){
this._phone_number = phone_number;
}


}

However when ever I try to run an add function like:

Database db = new Database(this);
db.addPub( new Pub(1,"The Old Queen's Head","53.3809646","-1.4631316","10:00","23:00","p","0","1","1","0","0","1","0","1","0","0","07983559073"));


I get this error:

E/SQLiteDatabase﹕ Error inserting OUTSIDE=1 PUB=p PHONE=07983559073 BEER=1 COCKTAIL=0 LNG=-1.4631316 NAME=The Old Queen's Head ID=1 CLOSE=23:00 LAT=53.3809646 SPORT=0 DART=0 QUIZ=0 OPEN=10:00 PAY=0 MUSIC=1 FOOD=1 POOL=0
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: pubsTable.ID (code 1555)
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:780)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
at apps.morgsssssss.pubmap.Database.addPub(Database.java:94)
at apps.morgsssssss.pubmap.MapsActivity.onCreate(MapsActivity.java:37)
at android.app.Activity.performCreate(Activity.java:6237)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)
at android.app.ActivityThread.-wrap11(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:5417)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)


And I cannot figure out what the cause is and how to edit my code.

Answer

I think you have defined pubsTable.ID as a primary key, and it already contains 1 so you must enter unique ID while adding a new Pub.

Comments