UNIQUE Constraint in SQLite

[racharambola5]

Hi, Can some one please help me with this problem.I am using SQLite database in my iphone app to save the values. 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")

[timle8n1]

Why do you think you can put the unique clause in your method definition? It would need to be part of the SQL.

However the way I deliver databases with my apps is to not create them in code - but to create them as part of the app bundle and copy them to the apps document directory on the first launch - I think most people do it that way. Then you can use something like the firefox sqlite manager to create the database.

[racharambola5]

Thanks for the reply..but I can't change the code now as the database is totally integrated across the classes. Can you please give me an example how to use the constraints with the code I have..

This is how I use my create table method

[db createTableNamed:@"Contacts" withField1:@"id" withField2:@"sugar_id" withField3:@"salutation" withField4:@"first_name" withField5:@"last_name" withField6:@"title" withField7:@"department" 
			  withField8:@"phone_home" withField9:@"phone_mobile" withField10:@"phone_work" withField11:@"phone_other" withField12:@"phone_fax" withField13:@"assistant" withField14:@"assistant_phone" 
			 withField15:@"primary_address_street" withField16:@"primary_address_city" withField17:@"primary_address_state" withField18:@"primary_address_postalcode" withField19:@"primary_address_country" 
			 withField20:@"alt_address_street" withField21:@"alt_address_city" withField22:@"alt_address_state" withField23:@"alt_address_postalcode" withField24:@"alt_address_country" withField25:@"date_entered" 
			 withField26:@"date_modified" withField27:@"deleted" withField28:@"do_not_call" withField29:@"birthdate" withField30:@"lead_source" withField31:@"description" withField32:@"email1" withField33:@"email2" 
			 withField34:@"account_name" withField35:@"account_id" withField36:@"custom"];

[dageshi]

UNIQUE(field3,field4,field31)

that line should not be where it is, it should be inside your sql statement.

And can I ask are you planning on dynamically changing your column names during application runtime? if not you'd me much better writing it like

NSString *sql = @"create database if not exists contacts(column1 text not null primary key, column2, e.t.c. et.c. )";

it would make your code significantly simpler and easier to maintain.

[racharambola5]

nope..I am not changing any column names during runtime..thought of writing methods to the class. Its my mistake..when I tried to include inside SQLite statement it says that the declaration of the method doesm't match with the statement you wrote..I know my code is messy but I can't change now..thanks for the reply and please help me regardin this