Many files:

  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys


innobase/btr/btr0cur.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/dict/dict0crea.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/dict/dict0dict.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/dict/dict0load.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/dict/dict0mem.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/btr0btr.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/dict0mem.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/ibuf0ibuf.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/os0file.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/os0sync.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/row0mysql.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/row0upd.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/sync0sync.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/trx0sys.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/trx0trx.h:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/ibuf0ibuf.ic:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/os0sync.ic:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/sync0sync.ic:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/include/trx0sys.ic:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/lock/lock0lock.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/pars/pars0opt.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/que/que0que.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/row/row0ins.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/row/row0mysql.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/row/row0sel.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/row/row0upd.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/srv/srv0srv.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/sync/sync0sync.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/trx/trx0sys.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys
innobase/trx/trx0trx.c:
  Implement ON DELETE CASCADE and facilitate switching off of UNIQUE constraints and foreign keys

4 files changed, 136 insertions, 37 deletions

diff --git a/innobase/dict/dict0crea.c b/innobase/dict/dict0crea.c
index f15d36251e4..38c09c1011d 100644
--- a/innobase/dict/dict0crea.c
+++ b/innobase/dict/dict0crea.c
@@ -1201,7 +1201,8 @@ loop:
 					ut_dulint_get_low(id),
 					table->name,
 					foreign->referenced_table_name,
-					foreign->n_fields);
+					foreign->n_fields
+					+ (foreign->type << 24));
 
 	for (i = 0; i < foreign->n_fields; i++) {
 
diff --git a/innobase/dict/dict0dict.c b/innobase/dict/dict0dict.c
index d52483074cd..a6f268c2153 100644
--- a/innobase/dict/dict0dict.c
+++ b/innobase/dict/dict0dict.c
@@ -1648,7 +1648,7 @@ dict_foreign_find_index(
 							->col->name;
 				if (ut_strlen(columns[i]) !=
 						ut_strlen(col_name)
-				    || 0 != ut_memcmp(columns[i],
+				    || 0 != ut_cmp_in_lower_case(columns[i],
 				    			col_name,
 				  			ut_strlen(col_name))) {
 				  	break;
@@ -1853,8 +1853,9 @@ dict_scan_col(
 	ibool*		success,/* out: TRUE if success */
 	dict_table_t*	table,	/* in: table in which the column is */
 	dict_col_t**	column,	/* out: pointer to column if success */
-	char**		column_name)/* out: pointer to column->name if
+	char**		column_name,/* out: pointer to column->name if
 				success */
+	ulint*		column_name_len)/* out: column name length */
 {
 	dict_col_t*	col;
 	char*		old_ptr;
@@ -1882,20 +1883,28 @@ dict_scan_col(
 		ptr++;
 	}
 
-	for (i = 0; i < dict_table_get_n_cols(table); i++) {
+	*column_name_len = (ulint)(ptr - old_ptr);
+	
+	if (table == NULL) {
+		*success = TRUE;
+		*column = NULL;
+		*column_name = old_ptr;
+	} else {
+	    	for (i = 0; i < dict_table_get_n_cols(table); i++) {
 
-		col = dict_table_get_nth_col(table, i);
+			col = dict_table_get_nth_col(table, i);
 
-		if (ut_strlen(col->name) == (ulint)(ptr - old_ptr)
-		    && 0 == ut_cmp_in_lower_case(col->name, old_ptr,
+			if (ut_strlen(col->name) == (ulint)(ptr - old_ptr)
+			    && 0 == ut_cmp_in_lower_case(col->name, old_ptr,
 						(ulint)(ptr - old_ptr))) {
-		    	/* Found */
+		    		/* Found */
 
-		    	*success = TRUE;
-		    	*column = col;
-		    	*column_name = col->name;
+		    		*success = TRUE;
+		    		*column = col;
+		    		*column_name = col->name;
 
-		    	break;
+		    		break;
+			}
 		}
 	}
 	
@@ -1914,14 +1923,18 @@ dict_scan_table_name(
 /*=================*/
 				/* out: scanned to */
 	char*		ptr,	/* in: scanned to */
-	dict_table_t**	table,	/* out: table object or NULL if error */
-	char*		name)	/* in: foreign key table name */
+	dict_table_t**	table,	/* out: table object or NULL */
+	char*		name,	/* in: foreign key table name */
+	ibool*		success,/* out: TRUE if ok name found */
+	char*		second_table_name)/* in/out: buffer where to store
+				the referenced table name; must be at least
+				2500 bytes */
 {
 	char*	dot_ptr			= NULL;
 	char*	old_ptr;
 	ulint	i;
-	char	second_table_name[10000];
 	
+	*success = FALSE;
 	*table = NULL;
 
 	while (isspace(*ptr)) {
@@ -1947,7 +1960,7 @@ dict_scan_table_name(
 		ptr++;
 	}
 
-	if (ptr - old_ptr > 9000) {
+	if (ptr - old_ptr > 2000) {
 		return(old_ptr);
 	}
 	
@@ -1978,6 +1991,8 @@ dict_scan_table_name(
 		second_table_name[ptr - old_ptr] = '\0';
 	}
 
+	*success = TRUE;
+
 	*table = dict_table_get_low(second_table_name);
 
 	if (*ptr == '`') {
@@ -2043,9 +2058,12 @@ dict_create_foreign_constraints(
 	ibool		success;
 	ulint		error;
 	ulint		i;
-	dict_col_t*	columns[1000];
-	char*		column_names[1000];
-
+	ulint		j;
+	dict_col_t*	columns[500];
+	char*		column_names[500];
+	ulint		column_name_lens[500];
+	char		referenced_table_name[2500];
+	
 	ut_ad(mutex_own(&(dict_sys->mutex)));
 
 	table = dict_table_get_low(name);
@@ -2090,7 +2108,7 @@ loop:
 	/* Scan the columns in the first list */
 col_loop1:
 	ptr = dict_scan_col(ptr, &success, table, columns + i,
-							column_names + i);
+				column_names + i, column_name_lens + i);
 	if (!success) {
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
@@ -2141,9 +2159,13 @@ col_loop1:
 					1 + ut_strlen(columns[i]->name));
 	}
 	
-	ptr = dict_scan_table_name(ptr, &referenced_table, name);
+	ptr = dict_scan_table_name(ptr, &referenced_table, name,
+					&success, referenced_table_name);
 
-	if (!referenced_table) {
+	/* Note that referenced_table can be NULL if the user has suppressed
+	checking of foreign key constraints! */
+
+	if (!success || (!referenced_table && trx->check_foreigns)) {
 		dict_foreign_free(foreign);
 
 		return(DB_CANNOT_ADD_CONSTRAINT);
@@ -2161,7 +2183,7 @@ col_loop1:
 
 col_loop2:
 	ptr = dict_scan_col(ptr, &success, referenced_table, columns + i,
-							column_names + i);
+				column_names + i, column_name_lens + i);
 	i++;
 	
 	if (!success) {
@@ -2183,43 +2205,104 @@ col_loop2:
 		return(DB_CANNOT_ADD_CONSTRAINT);
 	}
 
+	ptr = dict_accept(ptr, "ON", &success);
+
+	if (!success) {
+
+		goto try_find_index;
+	}
+
+	ptr = dict_accept(ptr, "DELETE", &success);
+
+	if (!success) {
+
+		goto try_find_index;
+	}
+
+	ptr = dict_accept(ptr, "CASCADE", &success);
+
+	if (success) {
+
+		foreign->type = DICT_FOREIGN_ON_DELETE_CASCADE;
+
+		goto try_find_index;
+	}
+
+	ptr = dict_accept(ptr, "SET", &success);
+
+	if (!success) {
+
+		goto try_find_index;
+	}
+
+	ptr = dict_accept(ptr, "NULL", &success);
+
+	if (success) {
+		for (j = 0; j < foreign->n_fields; j++) {
+			if ((dict_index_get_nth_type(
+				foreign->foreign_index, j)->prtype)
+				& DATA_NOT_NULL) {
+
+				/* It is not sensible to define SET NULL
+				if the column is not allowed to be NULL! */
+
+				dict_foreign_free(foreign);
+				return(DB_CANNOT_ADD_CONSTRAINT);
+			}
+		}
+
+		foreign->type = DICT_FOREIGN_ON_DELETE_SET_NULL;
+
+		goto try_find_index;
+	}
+	
+try_find_index:
 	/* Try to find an index which contains the columns as the first fields
 	and in the right order, and the types are the same as in
 	foreign->foreign_index */
 
-	index = dict_foreign_find_index(referenced_table, column_names, i,
+	if (referenced_table) {
+		index = dict_foreign_find_index(referenced_table,
+						column_names, i,
 						foreign->foreign_index);
-
-	if (!index) {
-		dict_foreign_free(foreign);
-		return(DB_CANNOT_ADD_CONSTRAINT);
+		if (!index) {
+			dict_foreign_free(foreign);
+			return(DB_CANNOT_ADD_CONSTRAINT);
+		}
+	} else {
+		ut_a(trx->check_foreigns == FALSE);
+		index = NULL;
 	}
 
 	foreign->referenced_index = index;
 	foreign->referenced_table = referenced_table;
 
 	foreign->referenced_table_name = mem_heap_alloc(foreign->heap,
-					1 + ut_strlen(referenced_table->name));
+					1 + ut_strlen(referenced_table_name));
 
-	ut_memcpy(foreign->referenced_table_name, referenced_table->name,
-					1 + ut_strlen(referenced_table->name));
+	ut_memcpy(foreign->referenced_table_name, referenced_table_name,
+					1 + ut_strlen(referenced_table_name));
 					
 	foreign->referenced_col_names = mem_heap_alloc(foreign->heap,
 							i * sizeof(void*));
 	for (i = 0; i < foreign->n_fields; i++) {
 		foreign->referenced_col_names[i]
 				= mem_heap_alloc(foreign->heap,
-					1 + ut_strlen(columns[i]->name));
-		ut_memcpy(
-			foreign->referenced_col_names[i], columns[i]->name,
-					1 + ut_strlen(columns[i]->name));
+					1 + column_name_lens[i]);
+		ut_memcpy(foreign->referenced_col_names[i], column_names[i],
+					column_name_lens[i]);
+		(foreign->referenced_col_names[i])[column_name_lens[i]] = '\0';
 	}
 
 	/* We found an ok constraint definition: add to the lists */
 	
 	UT_LIST_ADD_LAST(foreign_list, table->foreign_list, foreign);
-	UT_LIST_ADD_LAST(referenced_list, referenced_table->referenced_list,
+
+	if (referenced_table) {
+		UT_LIST_ADD_LAST(referenced_list,
+					referenced_table->referenced_list,
 								foreign);
+	}
 	goto loop;
 }
 
@@ -3034,6 +3117,14 @@ dict_print_info_on_foreign_keys_in_create_format(
 
 		buf2 += sprintf(buf2, ")");
 
+		if (foreign->type == DICT_FOREIGN_ON_DELETE_CASCADE) {
+			buf2 += sprintf(buf2, " ON DELETE CASCADE");
+		}
+	
+		if (foreign->type == DICT_FOREIGN_ON_DELETE_SET_NULL) {
+			buf2 += sprintf(buf2, " ON DELETE SET NULL");
+		}
+
 		foreign = UT_LIST_GET_NEXT(foreign_list, foreign);
 	}
 
diff --git a/innobase/dict/dict0load.c b/innobase/dict/dict0load.c
index 29c98db4a53..4917359c748 100644
--- a/innobase/dict/dict0load.c
+++ b/innobase/dict/dict0load.c
@@ -946,6 +946,11 @@ dict_load_foreign(
 	foreign->n_fields = mach_read_from_4(rec_get_nth_field(rec, 5, &len));
 
 	ut_a(len == 4);
+
+	/* We store the type to the bits 24-31 of n_fields */
+	
+	foreign->type = foreign->n_fields >> 24;
+	foreign->n_fields = foreign->n_fields & 0xFFFFFF;
 	
 	foreign->id = mem_heap_alloc(foreign->heap, ut_strlen(id) + 1);
 				
diff --git a/innobase/dict/dict0mem.c b/innobase/dict/dict0mem.c
index 1f9a44aca35..52f46062065 100644
--- a/innobase/dict/dict0mem.c
+++ b/innobase/dict/dict0mem.c
@@ -61,7 +61,8 @@ dict_mem_table_create(
 	table->mem_fix = 0;
 
 	table->n_mysql_handles_opened = 0;
-
+	table->n_foreign_key_checks_running = 0;
+		
 	table->cached = FALSE;
 	
 	table->cols = mem_heap_alloc(heap, (n_cols + DATA_N_SYS_COLS)
@@ -235,6 +236,7 @@ dict_mem_foreign_create(void)
 
 	foreign->id = NULL;
 
+	foreign->type = 0;
 	foreign->foreign_table_name = NULL;
 	foreign->foreign_table = NULL;
 	foreign->foreign_col_names = NULL;