UNIQUE Constraint in SQLite

Question

Hi, Can some one please help me with this problem.To save the values in my iphone app, I am using SQLite3. I have many duplicate values in my database. So, to eliminate that I am planning to write some unique constraint on the table. My constraint should be such a way that a contact whose firstname,lastname and email are same, I want to ignore that.

When I tried to implement the unique constraint in my table definition it's giving me an error. I tried this by checking the syntax in sqlite.org. Please see my table creation method and please help me. If you have any better solution please let me know. I will be glad to provide you any info if needed.

I have 36 fields in my database where field3 is firstname,field4 is lastname and field31 is email. Also, please let me know if we should use this constraint while inserting or selecting the rows. I will be waiting for your replies..Thank you!!!

The error in this method is: Expected : before ( token I tried in many ways but it didn't work.

-(void) createTableNamed:(NSString *) tableName withField1:(NSString *) field1 withField2:(NSString *) field2 withField3:(NSString *) field3 withField4:(NSString *) field4 
     withField5:(NSString *) field5 withField6:(NSString *) field6 withField7:(NSString *) field7 withField8:(NSString *) field8 withField9:(NSString *) field9 
     withField10:(NSString *) field10 withField11:(NSString *) field11 withField12:(NSString *) field12 withField13:(NSString *) field13 withField14:(NSString *) field14
     withField15:(NSString *) field15 withField16:(NSString *) field16 withField17:(NSString *) field17 withField18:(NSString *) field18 withField19:(NSString *) field19 
     withField20:(NSString *) field20 withField21:(NSString *) field21 withField22:(NSString *) field22 withField23:(NSString *) field23 withField24:(NSString *) field24 
     withField25:(NSString *) field25 withField26:(NSString *) field26 withField27:(NSString *) field27 withField28:(NSString *) field28 withField29:(NSString *) field29
     withField30:(NSString *) field30 withField31:(NSString *) field31 withField32:(NSString *) field32 withField33:(NSString *) field33 withField34:(NSString *) field34 
     withField35:(NSString *) field35 withField36:(NSString *) field36 UNIQUE(field3,field4,field31){
 char *err; 
 NSString *sql = [NSString stringWithFormat:
        @"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT, '%@' TEXT,UNIQUE('%@' TEXT,'%@' TEXT,'%@' TEXT));", tableName, field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15, field16, field17, field18, field19, field20, field21, field22, field23, field24, field25, field26, field27, field28, field29, field30, field31, field32, field33, field34, field35, field36,field3,field4,field31];
 if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) { 
  sqlite3_close(db); 
  NSAssert(0, @"Tabled failed to create.");
 }
 NSLog(@"Good job");
}

Answer 1

Don't enclose the table name or column names in single-quotes. In SQL, single-quotes are for string literals and date literals.

You should use double-quotes to delimit identifiers such as table names and column names. Since this is in a double-quoted string in your application code, you may have to escape these double-quotes. For example:

NSString *sql = [NSString stringWithFormat:
        @"CREATE TABLE IF NOT EXISTS \"%@\" (\"%@\" TEXT PRIMARY KEY ...

---

Looking more closely, I see your problem may not be in SQL but in Objective-C:

 withField36:(NSString *) field36 UNIQUE(field3,field4,field31){

Could it be the ( following UNIQUE in your method declarations is the one causing the error? I've never programmed in Objective-C, but this looks out of place.