Android SQLiteConstraintException: error code 19: constraint failed

Question

I've seen other questions about this exception, but all of them seem to be resolved with the solution that a row with the primary key specified already exists. This doesn't seem to be the case for me. I have tried replacing all single quotes in my strings with double quotes, but the same problem occurs.

I'm trying to insert a row into the Settings table of the SQLite database I've created by doing the following:

db.execSQL("DROP TABLE IF EXISTS "+Settings.SETTINGS_TABLE_NAME + ";");
db.execSQL(CREATE_MEDIA_TABLE);
db.execSQL(CREATE_SETTINGS_TABLE);
Cursor c = getAllSettings();
//If there isn't already a settings row, create a row full of defaults
if(c.getCount()==0){
    ContentValues cv  = new ContentValues();
    cv.put(Settings.SETTING_UNIQUE_ID, "'"+Settings.uniqueID+"'");          
    cv.put(Settings.SETTING_DEVICE_ID, Settings.SETTING_DEVICE_ID_DEFAULT);
    cv.put(Settings.SETTING_CONNECTION_PREFERENCE, Settings.SETTING_CONNECTION_PREFERENCE_DEFAULT);
    cv.put(Settings.SETTING_AD_HOC_ENABLED, Settings.SETTING_AD_HOC_ENABLED_DEFAULT);
    cv.put(Settings.SETTING_SERVER_ADDRESS, Settings.SETTING_SERVER_ADDRESS_DEFAULT);
    cv.put(Settings.SETTING_RECORDING_MODE, Settings.SETTING_RECORDING_MODE_DEFAULT);
    cv.put(Settings.SETTING_PREVIEW_ENABLED, Settings.SETTING_PREVIEW_ENABLED_DEFAULT);
    cv.put(Settings.SETTING_PICTURE_RESOLUTION_X, Settings.SETTING_PICTURE_RESOLUTION_X_DEFAULT);
    cv.put(Settings.SETTING_PICTURE_RESOLUTION_Y, Settings.SETTING_PICTURE_RESOLUTION_Y_DEFAULT);
    cv.put(Settings.SETTING_VIDEO_RESOLUTION_X, Settings.SETTING_VIDEO_RESOLUTION_X_DEFAULT);
    cv.put(Settings.SETTING_VIDEO_RESOLUTION_Y, Settings.SETTING_VIDEO_RESOLUTION_Y_DEFAULT);
    cv.put(Settings.SETTING_VIDEO_FPS, Settings.SETTING_VIDEO_FPS_DEFAULT);
    cv.put(Settings.SETTING_AUDIO_BITRATE_KBPS, Settings.SETTING_AUDIO_BITRATE_KBPS_DEFAULT);
    cv.put(Settings.SETTING_STORE_TO_SD, Settings.SETTING_STORE_TO_SD_DEFAULT);
    cv.put(Settings.SETTING_STORAGE_LIMIT_MB, Settings.SETTING_STORAGE_LIMIT_MB_DEFAULT);

    this.db.insert(Settings.SETTINGS_TABLE_NAME, null, cv);
}

The CREATE_SETTINGS_TABLE string is defined as the following:

private static String CREATE_SETTINGS_TABLE = "CREATE TABLE IF NOT EXISTS " + Settings.SETTINGS_TABLE_NAME + "("
+ Settings.SETTING_UNIQUE_ID + " TEXT NOT NULL PRIMARY KEY, "
+ Settings.SETTING_DEVICE_ID + " TEXT NOT NULL , "
+ Settings.SETTING_CONNECTION_PREFERENCE + " TEXT NOT NULL CHECK("+Settings.SETTING_CONNECTION_PREFERENCE+" IN("+Settings.SETTING_CONNECTION_PREFERENCE_ALLOWED+")), "
+ Settings.SETTING_AD_HOC_ENABLED + " TEXT NOT NULL CHECK("+Settings.SETTING_AD_HOC_ENABLED+" IN("+Settings.SETTING_AD_HOC_ENABLED_ALLOWED+")), "
+ Settings.SETTING_SERVER_ADDRESS + " TEXT NOT NULL, "
+ Settings.SETTING_RECORDING_MODE + " TEXT NOT NULL CHECK("+Settings.SETTING_RECORDING_MODE+" IN("+Settings.SETTING_RECORDING_MODE_ALLOWED+")), "
+ Settings.SETTING_PREVIEW_ENABLED + " TEXT NOT NULL CHECK("+Settings.SETTING_PREVIEW_ENABLED+" IN("+Settings.SETTING_PREVIEW_ENABLED_ALLOWED+")), "
+ Settings.SETTING_PICTURE_RESOLUTION_X + " TEXT NOT NULL, "
+ Settings.SETTING_PICTURE_RESOLUTION_Y + " TEXT NOT NULL, "
+ Settings.SETTING_VIDEO_RESOLUTION_X + " TEXT NOT NULL, "
+ Settings.SETTING_VIDEO_RESOLUTION_Y + " TEXT NOT NULL, "
+ Settings.SETTING_VIDEO_FPS + " TEXT NOT NULL, "
+ Settings.SETTING_AUDIO_BITRATE_KBPS + " TEXT NOT NULL, "
+ Settings.SETTING_STORE_TO_SD + " TEXT NOT NULL CHECK("+Settings.SETTING_STORE_TO_SD+" IN("+Settings.SETTING_STORE_TO_SD_ALLOWED+")), "
+ Settings.SETTING_STORAGE_LIMIT_MB + " TEXT NOT NULL )";

However, when I execute my insert, I always get:

03-19 19:37:36.974: ERROR/Database(386): Error inserting server_address='0.0.0.0' storage_limit='-1' connection='none' preview_enabled='0' sd_enabled='1' video_fps='15' audio_bitrate='96' device_id='-1' recording_mode='none' picture_resolution_x='-1' picture_resolution_y='-1' unique_id='000000000000000' adhoc_enable='0' video_resolution_x='320' video_resolution_y='240'
03-19 19:45:34.284: ERROR/Database(446): android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed

It seems as if all the columns in my insert are not null. The row's primary key HAS to be unique, because it's the only row in the table. Therefore, the only thing I can think of is my CHECK conditions aren't true. Here are the predefined strings I'm using:

public static final String SETTING_UNIQUE_ID = "unique_id";

public static final String SETTING_DEVICE_ID = "device_id";
public static final String SETTING_DEVICE_ID_DEFAULT = "'-1'";

public static final String SETTING_CONNECTION_PREFERENCE = "connection";
public static final String SETTING_CONNECTION_PREFERENCE_3G = "'3g'";
public static final String SETTING_CONNECTION_PREFERENCE_WIFI = "'wifi'";
public static final String SETTING_CONNECTION_PREFERENCE_NONE = "'none'";
public static final String SETTING_CONNECTION_PREFERENCE_ALLOWED = SETTING_CONNECTION_PREFERENCE_3G+","+SETTING_CONNECTION_PREFERENCE_WIFI+","+SETTING_CONNECTION_PREFERENCE_NONE;
public static final String SETTING_CONNECTION_PREFERENCE_DEFAULT = SETTING_CONNECTION_PREFERENCE_NONE;

public static final String SETTING_AD_HOC_ENABLED = "adhoc_enable";
public static final String SETTING_AD_HOC_ENABLED_ALLOWED = TRUE+","+FALSE;
public static final String SETTING_AD_HOC_ENABLED_DEFAULT = FALSE;

public static final String SETTING_SERVER_ADDRESS = "server_address";
public static final String SETTING_SERVER_ADDRESS_DEFAULT = "'0.0.0.0'";

public static final String SETTING_RECORDING_MODE = "recording_mode";
public static final String SETTING_RECORDING_MODE_VIDEO = "'video'";
public static final String SETTING_RECORDING_MODE_AUDIO = "'audio'";
public static final String SETTING_RECORDING_MODE_PICTURE = "'picture'";
public static final String SETTING_RECORDING_MODE_NONE = "'none'";
public static final String SETTING_RECORDING_MODE_ALLOWED = SETTING_RECORDING_MODE_VIDEO+","+SETTING_RECORDING_MODE_AUDIO+","+SETTING_RECORDING_MODE_PICTURE+","+SETTING_RECORDING_MODE_NONE;
public static final String SETTING_RECORDING_MODE_DEFAULT = SETTING_RECORDING_MODE_NONE;

public static final String SETTING_PREVIEW_ENABLED = "preview_enabled";
public static final String SETTING_PREVIEW_ENABLED_ALLOWED = TRUE+","+FALSE;
public static final String SETTING_PREVIEW_ENABLED_DEFAULT = FALSE;

public static final String SETTING_PICTURE_RESOLUTION_X = "picture_resolution_x";
public static final String SETTING_PICTURE_RESOLUTION_X_DEFAULT = "'-1'";

public static final String SETTING_PICTURE_RESOLUTION_Y = "picture_resolution_y";
public static final String SETTING_PICTURE_RESOLUTION_Y_DEFAULT = "'-1'";

public static final String SETTING_VIDEO_RESOLUTION_X = "video_resolution_x";
public static final String SETTING_VIDEO_RESOLUTION_X_DEFAULT = "'320'";

public static final String SETTING_VIDEO_RESOLUTION_Y = "video_resolution_y";
public static final String SETTING_VIDEO_RESOLUTION_Y_DEFAULT = "'240'";

public static final String SETTING_VIDEO_FPS = "video_fps";
public static final String SETTING_VIDEO_FPS_DEFAULT = "'15'";

public static final String SETTING_AUDIO_BITRATE_KBPS = "audio_bitrate";
public static final String SETTING_AUDIO_BITRATE_KBPS_DEFAULT = "'96'";

public static final String SETTING_STORE_TO_SD = "sd_enabled";
public static final String SETTING_STORE_TO_SD_ALLOWED = TRUE+","+FALSE;
public static final String SETTING_STORE_TO_SD_DEFAULT = TRUE;

public static final String SETTING_STORAGE_LIMIT_MB = "storage_limit";
public static final String SETTING_STORAGE_LIMIT_MB_DEFAULT = "'-1'";

public static final String SETTING_CLIP_LENGTH_SECONDS = "clip_length";
public static final String SETTING_CLIP_LENGTH_SECONDS_DEFAULT = "'300'";

Does anyone see what could be going on? I'm stumped. Thanks in advance.

Answer 1

It seems that the android put(string,string) method must already append quotes around your value string for you... as soon as I took the manual quotes I had been entering around my value strings out (but kept them in the "ALLOWED") strings I was passing to CHECK, the problem was solved.