sqlite 约束失败

Question

我正在开发一个基于sqlite的应用程序， 当我将数据插入数据库时​​，出现以下错误。

表结构：

CREATE TABLE "Products" ("ProductBarcode" VARCHAR PRIMARY KEY UNIQUE NOT NULL , "ProductName" VARCHAR NOT NULL , "ProductImage" VARCHAR NOT NULL , "ProductIngredients" VARCHAR NOT NULL , "ProductStatus" VARCHAR NOT NULL )代码

2011-04-15 10:09:48.408 halalgauge[4517:207]  Not Matched 
2011-04-15 10:09:48.410 halalgauge[4517:207] *** Assertion failure in -[sqlClass addRecord:], /Users/admin/Desktop/Halal/Classes/sqlClass.m:149
2011-04-15 10:09:48.410 halalgauge[4517:207] Exception occured at add statement, the error is Error while inserting data. 'constraint failed' 

是：

#import "sqlClass.h"

sqlite3 *database = nil;
sqlite3_stmt *deleteStmt = nil;
sqlite3_stmt *addStmt = nil;
sqlite3_stmt *detailStmt = nil;
sqlite3_stmt *updateStmt = nil;


@implementation sqlClass
@synthesize membersInfoArray,membersInfoDict,rowID;






- (void) copyDatabaseIfNeeded 
{
    membersInfoArray = [[NSMutableArray alloc]init];
    membersInfoDict = [[NSMutableDictionary alloc]init];

    //Using NSFileManager we can perform many file system operations.
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSString *dbPath = [self getDBPath];
    BOOL success = [fileManager fileExistsAtPath:dbPath]; 

    if(!success) {

        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"HalalGauge.sqlite"];
        success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];

        if (!success) 
            NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }   
    }

- (NSString *) getDBPath {


@try {

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"HalalGauge.sqlite"];


}
@catch (NSException * e) {
    NSLog(@"Exception");
}
@finally {
    //[sqlClass finalizeStatements];
    NSLog(@"At Finally block");
}

}



+ (void) finalizeStatements {

    if (addStmt) sqlite3_finalize(addStmt);
    if (database) sqlite3_close(database);
    if (deleteStmt) sqlite3_finalize(deleteStmt);
    if (detailStmt) sqlite3_finalize(detailStmt);
    if (updateStmt) sqlite3_finalize(updateStmt);
}

- (void) gettingData:(NSString *)dbPath {
    NSLog(@"Data base path is %@",dbPath);

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK)
    {
        const char *sql = "select * from Products";
        sqlite3_stmt *selectstmt;
        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) 
        {
            while(sqlite3_step(selectstmt) == SQLITE_ROW)
            {

                [membersInfoDict setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)] forKey:@"ProductBarcode"];
                [membersInfoDict setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)] forKey:@"ProductName"];
                [membersInfoDict setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 2)] forKey:@"ProductImage"];
                [membersInfoDict setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)] forKey:@"ProductIngredients"];
                [membersInfoDict setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)] forKey:@"ProductStatus"];

                if(membersInfoDict)
                {
                   [membersInfoArray addObject:membersInfoDict];
                   membersInfoDict = nil;
                //  NSLog(@"Entered and return");
                //  return;
                }
            }
        }

    }

    else
        sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
}


- (void) addRecord:(NSMutableDictionary *)recordDict
{
    @try {

        if(addStmt == nil) {
            const char *sql = "insert into Products (ProductBarcode,ProductName,ProductImage,ProductIngredients,ProductStatus) Values(?,?,?,?,?)";
            if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
                NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
        }

        sqlite3_bind_text(addStmt, 1, [[recordDict objectForKey:@"ProductBarcode"] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 2, [[recordDict objectForKey:@"ProductName"] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 3, [[recordDict objectForKey:@"ProductImage"] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 4, [[recordDict objectForKey:@"ProductIngredients"] UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 5, [[recordDict objectForKey:@"ProductStatus"] UTF8String], -1, SQLITE_TRANSIENT);

        //NSLog(@"the values are %@",addStmt);

        if(SQLITE_DONE != sqlite3_step(addStmt))
            NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
        else
            rowID = sqlite3_last_insert_rowid(database);
        NSLog(@"last inserted rowId = %d",rowID);

        sqlite3_close(database);



    }
    @catch (NSException * e) {
        NSLog(@"Exception occured at add statement, the error is %@ ",e);
    }
    @finally {
        [sqlClass finalizeStatements];
    }
        }





@end

Answer 1

可能不是“非空”约束而是“ProductBarcode”主键唯一？

你检查过产品条形码的唯一性吗？？？

Answer 2

我用以下代码解决了我的问题：

- (void) addRecord:(NSMutableDictionary *)recordDict
{
    @try {

        [self checkAndCreateDatabase];
        if (sqlite3_open([databasePath UTF8String], &database)== SQLITE_OK) {

            NSString *statement;
            sqlite3_stmt *compiledstatement;
            NSString *ProductName,*ProductBarcode,*ProductImage,*ProductIngredients,*ProductStatus;
            ProductName = [recordDict objectForKey:@"ProductName"];
            ProductBarcode = [recordDict objectForKey:@"ProductBarcode"];
            ProductImage = [recordDict objectForKey:@"ProductImage"];
            ProductIngredients = [recordDict objectForKey:@"ProductIngredients"];
            ProductStatus = [recordDict objectForKey:@"ProductStatus"];



            statement = [[NSString alloc]initWithFormat:@"insert into Products values('%@','%@','%@','%@','%@')",ProductBarcode,ProductName,ProductImage,ProductIngredients,ProductStatus];
            const char *sqlstatement = [statement UTF8String];
            if (sqlite3_prepare_v2(database, sqlstatement, -1, &compiledstatement, NULL)== SQLITE_OK) {
                if (SQLITE_DONE!=sqlite3_step(compiledstatement) ) {
                    NSAssert1(0,@"Error when inserting  %s",sqlite3_errmsg(database));

                }
                else {
                    NSLog(@"Data inserted Successfully");
                }

            sqlite3_finalize(compiledstatement);

            }
            sqlite3_close(database);
        }

    }
    @catch (NSException * e) {
        NSLog(@"The Record already inserted ");
    }
}