ItGo.me Focus on IT Recommend

Home > java - sqlite constraint exception primary key must be unique

java - sqlite constraint exception primary key must be unique

I have 3 tables

Table 1 : Member - To store every information about the member,

Attribute : MemberUsername(PK), MemberPassword

Table 2 : MsGroup - To store every registered group

Attribute : GroupId(PK), GroupName, GroupDescription

Table 3 : MsGroupDetail - To store list of every username in every group

Attribute : GroupId(PK,FK), MemberUsername(PK,FK)

My Query for Creating that 3 tables :

        db.execSQL("CREATE TABLE IF NOT EXISTS " + MS_MEMBER + " ("
                + MEMBER_USERNAME + " TEXT PRIMARY KEY NOT NULL, "
                + MEMBER_PASSWORD + " TEXT NOT NULL, " + MEMBER_EMAIL
                + " TEXT NOT NULL" + ");");

        db.execSQL("CREATE TABLE IF NOT EXISTS " + MS_GROUP + " ("
                + GROUP_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + GROUP_NAME + " TEXT NOT NULL, " + GROUP_DESCRIPTION
                + " TEXT NOT NULL);");
        db.execSQL("CREATE TABLE IF NOT EXISTS " + MS_GROUP_DETAIL + " ("
                + GROUP_ID + " INTEGER PRIMARY KEY, " + MEMBER_USERNAME
                + " TEXT,  FOREIGN KEY (" + MEMBER_USERNAME
                + ") REFERENCES " + MS_MEMBER + "(" + MEMBER_USERNAME
                + "), FOREIGN KEY (" + GROUP_ID + ") REFERENCES "
                + MS_GROUP + "(" + GROUP_ID + "));");

I have successfully make some code to create member, create group, but the problem is I failed to create a query to invite/add a new member to a group (Failed to insert to MsGroupDetail)

This is the syntax for adding GroupMember

public void addGroupMember(String groupId, String username) {
    // TODO Auto-generated method stub
    ContentValues cv2 = new ContentValues();
    cv2.put(GROUP_ID, groupId);
    cv2.put(MEMBER_USERNAME, username);
    ourDatabase.insert(MS_GROUP_DETAIL, null, cv2);
}

And this is the syntax to see the list of my Groups

public String[] fetchGroupName(String username) {
    int i = 0;

    String Query = "SELECT " + GROUP_NAME + " From " + MS_GROUP
            + " a INNER JOIN " + MS_GROUP_DETAIL + " b ON a." + GROUP_ID
            + "=b." + GROUP_ID + " WHERE " + MEMBER_USERNAME + "=?";
    Cursor c = ourDatabase.rawQuery(Query, new String[] { username });
    String groupName[] = new String[c.getCount()];
    int iGroupName = c.getColumnIndex(GROUP_NAME);

    c.moveToFirst();
    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        groupName[i] = c.getString(iGroupName);
        i++;
    }

    c.close();
    return groupName;
}

Everytime I invite a new member/adding to MsGroupDetail table, I failed to insert it to MsGroupDetail and the log cat said : "SQLite Constraint Exception, primary key must be unique"

*nb : My MsGroupDetail is once again to list every member within a group,

I have tested the same concept in Sql Server Management Studio 2008 and It worked, aI dont know any better concept than what I've coded above,

Can you guys please tell me is there any solution for this?

Thank you so much..

Recommend:Android Realm - Primary Key Constraint Exception & Duplication

the question here.. I'm writing to Realm from my JSONfile, which is recorded and reflected in the RealmBrowser. But when I restart my app, I'm getting error on io.realm.exceptions.RealmPrimaryKeyConstraintException: Value already exists: 2

answer 1 >>---Accepted---Accepted---Accepted---

The problem is your GROUP_ID is not unique. If you have two members in group 1, your table will have two entries --

1, Member 1
1, Member 2

By specifying "PRIMARY KEY" on GROUP_ID you are stating it will be unique. You need different primary key. In android, it is common to use "_id", something like --

db.execSQL("CREATE TABLE IF NOT EXISTS " + MS_GROUP_DETAIL + " ("
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + GROUP_ID + " INTEGER, " + MEMBER_USERNAME
                + " TEXT,  FOREIGN KEY (" + MEMBER_USERNAME
                + ") REFERENCES " + MS_MEMBER + "(" + MEMBER_USERNAME
                + "), FOREIGN KEY (" + GROUP_ID + ") REFERENCES "
                + MS_GROUP + "(" + GROUP_ID + "));");

Recommend:Android SQLite PRIMARY KEY must be unique exception when primary keys are unique

erty") I get an SQLLiteConstraintException with the message "PRIMARY KEY must be unique (code 19)". My primary keys are all unique. I suspect this is happening because the primary keys are not in order. The query I'm doing from the databas

oriUlr:http://stackoverflow.com/questions/26986687/sqlite-constraint-exception-primary-key-must-be-unique

------splitte line----------------------------