When attempting to delete a field that is part of a foreign key relationship, remove the key before the field to prevent an error. Obviously warn the user that the key is being dropped as well. Fixes issue #462.

This check also needs to be implemented when removing indexes as foreign keys depend on the presence of indexes to prevent table scans.

Something to be discussed, the display of foreign keys that are made up of multiple fields in the relations table and link arrows in the content table.

5 files changed, 71 insertions, 14 deletions

diff --git a/Source/SPArrayAdditions.m b/Source/SPArrayAdditions.m
index c16616a5..4742aa83 100644
--- a/Source/SPArrayAdditions.m
+++ b/Source/SPArrayAdditions.m
@@ -54,7 +54,7 @@
 	for (NSString *component in self)
 	{
 		if ([result length])
-			[result appendString:@","];
+			[result appendString:@", "];
 
 		[result appendString:component];
 	}
diff --git a/Source/SPTableData.m b/Source/SPTableData.m
index 0301fd41..c1fd0497 100644
--- a/Source/SPTableData.m
+++ b/Source/SPTableData.m
@@ -267,7 +267,7 @@
 	NSDictionary *columnData;
 	NSEnumerator *enumerator;
 
-	if( [tableListInstance tableType] == SP_TABLETYPE_TABLE || [tableListInstance tableType] == SP_TABLETYPE_VIEW ) {
+	if( [tableListInstance tableType] == SP_TABLETYPE_TABLE || [tableListInstance tableType] == SP_TABLETYPE_VIEW ) {		
 		tableData = [self informationForTable:[tableListInstance tableName]];
 	}
 	
@@ -437,8 +437,16 @@
 				[fieldsParser setString:[[parts objectAtIndex:1] stringByTrimmingCharactersInSet:bracketSet]];
 				[constraintDetails setObject:[fieldsParser unquotedString] forKey:@"name"];
 
-				[fieldsParser setString:[[parts objectAtIndex:4] stringByTrimmingCharactersInSet:bracketSet]];
-				[constraintDetails setObject:[fieldsParser unquotedString] forKey:@"columns"];
+				NSMutableArray *keyColumns = [NSMutableArray array];
+				NSArray *keyColumnStrings = [[[parts objectAtIndex:4] stringByTrimmingCharactersInSet:bracketSet] componentsSeparatedByString:@","];
+				
+				for (NSString *keyColumn in keyColumnStrings)
+				{
+					[fieldsParser setString:[[keyColumn stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]] stringByTrimmingCharactersInSet:bracketSet]];
+					[keyColumns addObject:[fieldsParser unquotedString]];
+				}
+				
+				[constraintDetails setObject:keyColumns forKey:@"columns"];
 
 				[fieldsParser setString:[[parts objectAtIndex:6] stringByTrimmingCharactersInSet:bracketSet]];
 				[constraintDetails setObject:[fieldsParser unquotedString] forKey:@"ref_table"];
diff --git a/Source/SPTableRelations.m b/Source/SPTableRelations.m
index d0ebb11b..29719f0b 100644
--- a/Source/SPTableRelations.m
+++ b/Source/SPTableRelations.m
@@ -455,7 +455,7 @@
 		{
 			[relationData addObject:[NSDictionary dictionaryWithObjectsAndKeys:
 									[constraint objectForKey:@"name"], @"name",
-									[constraint objectForKey:@"columns"], @"columns",
+									[[constraint objectForKey:@"columns"] objectAtIndex:0], @"columns",
 									[constraint objectForKey:@"ref_table"], @"fk_table",
 									[constraint objectForKey:@"ref_columns"], @"fk_columns",
 									[constraint objectForKey:@"update"], @"on_update",
diff --git a/Source/TableContent.m b/Source/TableContent.m
index 26d2138e..f8b29c28 100644
--- a/Source/TableContent.m
+++ b/Source/TableContent.m
@@ -254,10 +254,13 @@
 	
 	// Retrieve the constraints, and loop through them to add up to one foreign key to each column
 	NSArray *constraints = [tableDataInstance getConstraints];
-	for (NSDictionary *constraint in constraints) {
-		NSString *firstColumn = [[[constraint objectForKey:@"columns"] componentsSeparatedByString:@","] objectAtIndex:0];
+	
+	for (NSDictionary *constraint in constraints) 
+	{
+		NSString *firstColumn    = [[constraint objectForKey:@"columns"] objectAtIndex:0];
 		NSString *firstRefColumn = [[[constraint objectForKey:@"ref_columns"] componentsSeparatedByString:@","] objectAtIndex:0];
-		NSUInteger columnIndex = [columnNames indexOfObject:firstColumn];
+		NSUInteger columnIndex   = [columnNames indexOfObject:firstColumn];
+				
 		if (columnIndex != NSNotFound && ![[dataColumns objectAtIndex:columnIndex] objectForKey:@"foreignkeyreference"]) {
 			NSDictionary *refDictionary = [NSDictionary dictionaryWithObjectsAndKeys:
 											[constraint objectForKey:@"ref_table"], @"table",
@@ -282,7 +285,7 @@
 	// Add the new columns to the table
 	for ( i = 0 ; i < [dataColumns count] ; i++ ) {
 		columnDefinition = NSArrayObjectAtIndex(dataColumns, i);
-
+		
 		// Set up the column
 		theCol = [[NSTableColumn alloc] initWithIdentifier:[columnDefinition objectForKey:@"datacolumnindex"]];
 		[[theCol headerCell] setStringValue:[columnDefinition objectForKey:@"name"]];
diff --git a/Source/TableSource.m b/Source/TableSource.m
index 6228e6f1..8fc9c1d4 100644
--- a/Source/TableSource.m
+++ b/Source/TableSource.m
@@ -326,12 +326,29 @@ loads aTable, put it in an array, update the tableViewColumns and reload the tab
 	}
 	
 	NSString *field = [[tableFields objectAtIndex:[tableSourceView selectedRow]] objectForKey:@"Field"];
-
+	
+	BOOL hasForeignKey = NO;
+	NSString *referencedTable = @"";
+		
+	// Check to see whether the user is attempting to remove a field that has foreign key constraints and thus
+	// would result in an error if not dropped before removing the field.
+	for (NSDictionary *constraint in [tableDataInstance getConstraints])
+	{
+		for (NSString *column in [constraint objectForKey:@"columns"])
+		{
+			if ([column isEqualToString:field]) {
+				hasForeignKey = YES;
+				referencedTable = [constraint objectForKey:@"ref_table"];
+				break;
+			}
+		}
+	}
+	
 	NSAlert *alert = [NSAlert alertWithMessageText:[NSString stringWithFormat:NSLocalizedString(@"Delete field '%@'?", @"delete field message"), field]
 									 defaultButton:NSLocalizedString(@"Delete", @"delete button")
 								   alternateButton:NSLocalizedString(@"Cancel", @"cancel button") 
 									   otherButton:nil 
-						 informativeTextWithFormat:[NSString stringWithFormat:NSLocalizedString(@"Are you sure you want to delete the field '%@'? This action cannot be undone.", @"delete field informative message"), field]];
+						 informativeTextWithFormat:(hasForeignKey) ? [NSString stringWithFormat:NSLocalizedString(@"This field is part of a foreign key relationship with the table '%@'. This relationship must be removed before the field can be deleted.\n\nAre you sure you want to continue to remove the relationship and the field? This action cannot be undone.", @"delete field and foreign key informative message"), referencedTable] : [NSString stringWithFormat:NSLocalizedString(@"Are you sure you want to delete the field '%@'? This action cannot be undone.", @"delete field informative message"), field]];
 	
 	[alert setAlertStyle:NSCriticalAlertStyle];
 	
@@ -342,7 +359,7 @@ loads aTable, put it in an array, update the tableViewColumns and reload the tab
 	[[buttons objectAtIndex:0] setKeyEquivalentModifierMask:NSCommandKeyMask];
 	[[buttons objectAtIndex:1] setKeyEquivalent:@"\r"];
 	
-	[alert beginSheetModalForWindow:tableWindow modalDelegate:self didEndSelector:@selector(sheetDidEnd:returnCode:contextInfo:) contextInfo:@"removefield"];
+	[alert beginSheetModalForWindow:tableWindow modalDelegate:self didEndSelector:@selector(sheetDidEnd:returnCode:contextInfo:) contextInfo:(hasForeignKey) ? @"removeFieldAndForeignKey" : @"removeField"];
 }
 
 /**
@@ -864,10 +881,39 @@ fetches the result as an array with a dictionary for each row in it
 		}
 		[tableSourceView reloadData];
 	} 
-	else if ([contextInfo isEqualToString:@"removefield"]) {
+	else if ([contextInfo isEqualToString:@"removeField"] || [contextInfo isEqualToString:@"removeFieldAndForeignKey"]) {
 		if (returnCode == NSAlertDefaultReturn) {
 			
-			// Remove row
+			// Remove the foreign key before the field if required
+			if ([contextInfo isEqualToString:@"removeFieldAndForeignKey"]) {
+			
+				NSString *relationName = @"";
+				NSString *field = [[tableFields objectAtIndex:[tableSourceView selectedRow]] objectForKey:@"Field"];
+				
+				// Get the foreign key name
+				for (NSDictionary *constraint in [tableDataInstance getConstraints])
+				{
+					for (NSString *column in [constraint objectForKey:@"columns"])
+					{
+						if ([column isEqualToString:field]) {
+							relationName = [constraint objectForKey:@"name"];
+							break;
+						}
+					}
+				}
+				
+				[mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ DROP FOREIGN KEY %@", [selectedTable backtickQuotedString], [relationName backtickQuotedString]]];
+								
+				if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+					
+					NSBeginAlertSheet(NSLocalizedString(@"Unable to remove relation", @"error removing relation message"), 
+									  NSLocalizedString(@"OK", @"OK button"),
+									  nil, nil, [NSApp mainWindow], nil, nil, nil, nil, 
+									  [NSString stringWithFormat:NSLocalizedString(@"An error occurred while trying to remove the relation '%@'.\n\nMySQL said: %@", @"error removing relation informative message"), [relationName backtickQuotedString], [mySQLConnection getLastErrorMessage]]);	
+				} 
+			}
+			
+			// Remove field
 			[mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ DROP %@",
 										  [selectedTable backtickQuotedString], [[[tableFields objectAtIndex:[tableSourceView selectedRow]] objectForKey:@"Field"] backtickQuotedString]]];