Why am i getting SQLlite constraint error (Code=19) even if i am not leaving any fields null?

Question

I am trying to create a Admin table.Taking the input from the app and adding it to the table.My admin class is used for getting and setting the values . I tried a lot but unable to identify the issue yet .

This is from my Data Base Helper class

    @Override
public void onCreate(SQLiteDatabase db) {
    String query_create_administrator="create table "+TBL_ADMINISTRATOR+" ( "+ADMIN_ID+" INTEGER PRIMARY KEY, "+ADMIN_NAME+
            " TEXT, "+ADMIN_MAIL+" TEXT, "+ADMIN_PASSWORD+" TEXT, "+ADMIN_CONTACT+" TEXT, "+ADMIN_ADDRESS+" TEXT, "+
            ADMIN_ROLE+" TEXT, "+STATUS+" TEXT)";

    String query_create_student="create table "+TBL_STUDENT+" ( "+STUDENT_ID+" INTEGER PRIMARY KEY, "
            +STUDENT_PID+" TEXT, "+STUDENT_NAME+" TEXT, "+STUDENT_MAIL+" TEXT, "+STUDENT_PASSWORD+" TEXT, "
            +STUDENT_CONTACT+" TEXT, "+STUDENT_ADDRESS+" TEXT, "+STUDENT_COURSE+" TEXT, "+STUDENT_YEAR+" TEXT, "
            +STUDENT_BRANCH+" TEXT)";

    db.execSQL(query_create_administrator);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

public long registerAdmin(Administrator obj){
    SQLiteDatabase db=getWritableDatabase();
    ContentValues cv=new ContentValues();

    cv.put(ADMIN_ID,obj.getId());
    cv.put(ADMIN_NAME,obj.getName());
    cv.put(ADMIN_MAIL,obj.getMail());
    cv.put(ADMIN_PASSWORD,obj.getPassword());
    cv.put(ADMIN_CONTACT,obj.getContact());
    cv.put(ADMIN_ADDRESS,obj.getAddress());
    cv.put(ADMIN_ROLE,obj.getRole());
    cv.put(STATUS,obj.getStatus());

    db.insert(TBL_ADMINISTRATOR,null,cv);
    long done=3;

    return done;
}

This is the method from the activity class

public void register(View v){
    long check=0;
    obj=new Administrator();
    SchoolDBHandler db=new SchoolDBHandler(this);
    String n,m,p,c,a;
    n=name.getText().toString();
    m=mail.getText().toString();
    p=pass.getText().toString();
    c=contact.getText().toString();
    a=address.getText().toString();
    if(n.equalsIgnoreCase("")||m.equalsIgnoreCase("")||p.equalsIgnoreCase("")||c.equalsIgnoreCase("")||a.equalsIgnoreCase(""))
    {
        Toast.makeText(getApplicationContext(),"All fields are mandatory",Toast.LENGTH_SHORT).show();
    }
    else {

        obj.setName(n);
        obj.setMail(m);
        obj.setPassword(p);
        obj.setContact(c);
        obj.setAddress(a);
        obj.setStatus("Active");
        if(role_select2!=null)obj.setRole(role_select2);
        check=db.registerAdmin(obj);
        if(check>0) {
            resetEditTextxs();
            Toast.makeText(getApplicationContext(),"Successfully Created",Toast.LENGTH_SHORT).show();
        }
        else Toast.makeText(getApplicationContext(),"Error",Toast.LENGTH_SHORT).show();
    }

}

This is the error log:

E/Database: Error inserting status=Active address=AAA email=AAA name=AAA role=parent contact=1211 eid=0 pass=AAA android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed at android.database.sqlite.SQLiteStatement.native_execute(Native Method) at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:61) at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1582) at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1426) at com.thebitshoes.classproject.dao.SchoolDBHandler.registerAdmin(SchoolDBHandler.java:191) at com.thebitshoes.classproject.AdminSignUp.register(AdminSignUp.java:109) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:507) at android.support.v7.app.AppCompatViewInflater$DeclaredOnClickListener.onClick(AppCompatViewInflater.java:288) at android.view.View.performClick(View.java:2485) at android.view.View$PerformClick.run(View.java:9080) at android.os.Handler.handleCallback(Handler.java:587) at android.os.Handler.dispatchMessage(Handler.java:92) at android.os.Looper.loop(Looper.java:130) at android.app.ActivityThread.main(ActivityThread.java:3683) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:507) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597) at dalvik.system.NativeStart.main(Native Method)

Answer 1

Your "onCreate:"

public void onCreate(SQLiteDatabase db) {
    String query_create_administrator="create table "+TBL_ADMINISTRATOR+" ( "+ADMIN_ID+" INTEGER PRIMARY KEY, "+ADMIN_NAME+
            " TEXT, "+ADMIN_MAIL+" TEXT, "+ADMIN_PASSWORD+" TEXT, "+ADMIN_CONTACT+" TEXT, "+ADMIN_ADDRESS+" TEXT, "+
            ADMIN_ROLE+" TEXT, "+STATUS+" TEXT)";
<= ADMIN_ID is your primary key: it cannot be null

Your registerAdmin":

public long registerAdmin(Administrator obj){
    SQLiteDatabase db=getWritableDatabase();
    ContentValues cv=new ContentValues();

    cv.put(ADMIN_ID,obj.getId());
    cv.put(ADMIN_NAME,obj.getName());
    cv.put(ADMIN_MAIL,obj.getMail());
    cv.put(ADMIN_PASSWORD,obj.getPassword());
    cv.put(ADMIN_CONTACT,obj.getContact());
    cv.put(ADMIN_ADDRESS,obj.getAddress());
    cv.put(ADMIN_ROLE,obj.getRole());
    cv.put(STATUS,obj.getStatus());

    db.insert(TBL_ADMINISTRATOR,null,cv);
<= It looks like you're TRYING to assign an admin_id...

Your error message:

   status=Active 
   address=AAA 
   email=AAA name=AAA 
   role=parent 
   contact=1211 
   eid=0 
   <= But ADMIN_ID doesn't appear to be in the actual "insert"

You haven't shown us how you've defined class "Administrator" (the "obj" parameter in registerAdmin()), nor what the "ADMIN_ID" column name actually is (so we don't really know how to map the names in your error message with the names in your definition).

But the message is definitely occurring in "registerAdmin()", and, because you have only one required field ("ADMIN_ID"), that's definitely the place to start looking.

'Hope that helps!

==========================================================

UPDATE: There are several different questions here:

1. Q: Why am I getting SQLiteConstraintException: error code 19?

   A: The error is coming from registerAdmin(), column ADMIN_ID (aka "eid"). You've apparently already registered an administrator, with the duplicate ID "0".

   Don't do that :) Primary keys must be non-null, and must be unique.

2. Q: Should I let it be auto incremented by default?

   A: In general, for most other databases, I'd reply "sure!"

   However, I've seen some articles discouraging AUTO_INCREMENT in SQLLite. For example: http://www.sqlitetutorial.net/sqlite-autoincrement/

   I'll leave the choice up to you. If you allow SQLLite to assign the ID, however, make sure it's in sync with your Java "Administrator" object (obj).

3. ALSO: you might not WANT to insert, if the administrator already exists.

   If you want to modify an EXISTING administrator, then you want to UPDATE the row. Look here for more details:

   SQLite UPSERT / UPDATE OR INSERT

       -- Try to update any existing row
       UPDATE players
       SET user_name='steven', age=32
       WHERE user_name='steven';

       -- If no update happened (i.e. the row didn't exist) then insert one
       INSERT INTO players (user_name, age)
       SELECT 'steven', 32
       WHERE (Select Changes() = 0);

Again - I hope that helps!

PS: Please consider renaming eid to ADMIN_ID, or admin_id.

And don't forget to apply the same principles when you register students!