ios - 'INSERT INTO' 突然返回错误 'PRIMARY KEY must be unique'

Question

我的 iOS 应用程序上有一个 sqlite 数据库。我发现列的编号有误，然后我更改为正确的编号，从那以后我收到错误“PRIMARY KEY 必须是唯一的”。在错误发生之前一切顺利(除了 2 列错误放置的值)。

我的代码:

- (BOOL)createGroupMemberTable {
    char *error = NULL;
    const char *sqlStatment = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' (ID INTEGER PRIMARY KEY AUTOINCREMENT, %@ INTEGER, %@ INTEGER, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT);", kTableNameGroupMember, kMemberID, kGroupID, kName, kTime, kLatitude, kLongitude, kMobile, kVisible, kAddress, kPicture].UTF8String;
    int responseCode = sqlite3_exec(_database, sqlStatment, NULL, NULL, &error);
    
    if (responseCode != SQLITE_OK) {
        sqlite3_close(_database);
        printf("Table 'GroupMember' failed to create, responseCode: %i\n", responseCode);
        return NO;
    }
    else {
        return YES;
    }
}

- (BOOL)addGroupMember:(GroupMember*)aGroupMember {
    char *error = NULL;
    const char *sqlStatment = [NSString stringWithFormat:@"INSERT INTO %@ ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@') VALUES (%i, %i, '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@');", kTableNameGroupMember, kMemberID, kGroupID, kName, kTime, kLatitude, kLongitude, kMobile, kVisible, kAddress, kPicture, aGroupMember.memberID, aGroupMember.groupID, aGroupMember.name, aGroupMember.time, aGroupMember.latitude, aGroupMember.longitude, aGroupMember.mobile, aGroupMember.visible, aGroupMember.address, aGroupMember.picture].UTF8String;
    int responseCode = sqlite3_exec(_database, sqlStatment, NULL, NULL, &error);
    
    if (responseCode != SQLITE_OK) {
        printf("\n\nFailed to insert into 'GroupMember' table, responseCode: %i\n", responseCode);
        printf("Error Message: %s\n\n", sqlite3_errmsg(_database));
        return NO;
    }
    else {
        return YES;
    }
}

这可能无关紧要，但这是我创建数据库的代码:

static SQLiteHandler *_database;

+ (SQLiteHandler*)database {
    if (_database == nil) {
        _database = [[SQLiteHandler alloc] init];
    }
    return _database;
}

- (NSString*)databasePath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docDirectory = [paths objectAtIndex:0];
    return [docDirectory stringByAppendingPathComponent:@"social.sqlite3"];
}

- (id)init {
    if ((self = [super init])) {
        const char *fileName = [self databasePath].UTF8String;
        
        if ([[NSFileManager defaultManager] fileExistsAtPath:[NSString stringWithFormat:@"%s", fileName]]) {
            printf("SQLITE EXISTS\n");
        }
        else {
             printf("SQLITE NOT EXISTS\n");
            const char *finalPath = [self databasePath].UTF8String;
            printf("File 'social.sqlite3' created at path: %s\n", finalPath);
        }
           
        
        printf("fileName: %s\n", fileName);
        int responseCode = sqlite3_open(fileName, &_database);
        
        if (responseCode != SQLITE_OK) {
            printf("Failed to open database, responseCode: %i\n", responseCode);
        }
        
        [self createGroupTable];
        [self createGroupMemberTable];
    }
    return self;
}

错误日志:

Failed to insert into 'GroupMember' table, responseCode: 19 ErrorMessage: PRIMARY KEY must be unique

define SQLITE_CONSTRAINT 19 /* Abort due to constraint violation */

有什么想法吗？

编辑:

答案正如我最终认为的那样，就像“Petesh”在这里评论的那样。所有答案都是正确的，所以我会标记第一个。

Answer 1

您正在插入具有相同主键(成员 ID)的组成员。您应该尝试检查此成员(member) ID 是否存在于数据库中。

如果组成员已经存在，您还可以决定替换所有值，使用 INSERT OR REPLACE INTO:

[NSString stringWithFormat:@"INSERT OR REPLACE INTO %@ ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@') VALUES (%i, %i, '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@');", kTableNameGroupMember, kMemberID, kGroupID, kName, kTime, kLatitude, kLongitude, kMobile, kVisible, kAddress, kPicture, aGroupMember.memberID, aGroupMember.groupID, aGroupMember.name, aGroupMember.time, aGroupMember.latitude, aGroupMember.longitude, aGroupMember.mobile, aGroupMember.visible, aGroupMember.address, aGroupMember.picture].UTF8String;