sqlite约束异常主键必须唯一 [英] sqlite constraint exception primary key must be unique

问题描述

我有3张桌子

表1：成员-要存储有关成员的所有信息，

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

属性：MemberUsername（PK），MemberPassword

Attribute : MemberUsername(PK), MemberPassword

表2：MsGroup-存储每个已注册的组

Table 2 : MsGroup - To store every registered group

属性： GroupId（PK），GroupName，GroupDescription

Attribute : GroupId(PK), GroupName, GroupDescription

表3：MsGroupDetail-存储每个组中每个用户名的列表

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

属性：GroupId（PK，FK），MemberUsername（PK，FK）

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

我对创建3个表的查询：

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 + "));");

我已经成功创建了一些代码来创建成员，创建组，但是问题是我无法创建邀请/将新成员添加到组的查询（无法插入MsGroupDetail）

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)

这是添加GroupMember的语法

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;
}

每次我邀请新成员/添加到MsGroupDetail表时，我都无法插入它对MsGroupDetail和日志猫说： SQLite约束异常，主键必须是唯一的

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：我的MsGroupDetail再次列出了组中的每个成员，

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

我已经在Sql Server Management Studio 2008中测试了相同的概念，并且奏效了，我不知道有什么比以上代码更好的概念了，

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?

非常感谢..

推荐答案

问题是您的 GROUP_ID 不是唯一的。如果组1中有两个成员，则表中将有两个条目-

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

通过在 GROUP_ID 您声明它将是唯一的。您需要其他主键。在android中，通常使用 _id，例如-

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 + "));");

这篇关于sqlite约束异常主键必须唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！