Z8g2kx3z

1SET foreign_key_checks = 0;
2
3ALTER TABLE `escolaterrafir`.`t23_aluno` MODIFY COLUMN `a21_saida_id` INTEGER DEFAULT NULL;
4	
5------------------------
6LATEST FOREIGN KEY ERROR
7------------------------
8090506 11:57:34 Error in foreign key constraint of table escolaterrafir/t23_aluno:
9there is no index in the table which would contain
10the columns as the first columns, or the data types in the
11table do not match to the ones in the referenced table
12or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
13,
14  CONSTRAINT FK_t23_aluno_8 FOREIGN KEY (a21_saida_id) REFERENCES t21_turma (A21_ID)
15	
16DROP TABLE IF EXISTS `escolaterrafir`.`t21_turma`;
17CREATE TABLE  `escolaterrafir`.`t21_turma` (
18  `A21_ID` int(10) unsigned NOT NULL auto_increment,
19  ...
20) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
21	
22DROP TABLE IF EXISTS `escolaterrafir`.`t23_aluno`;
23CREATE TABLE  `escolaterrafir`.`t23_aluno` (
24  ...
25  `a21_saida_id` int(10) unsigned default NULL,
26  ...
27  KEY `Index_7` (`a23_id_pedagogica`),
28  ...
29  CONSTRAINT `FK_t23_aluno_8` FOREIGN KEY (`a21_saida_id`) REFERENCES `t21_turma` (`A21_ID`)
30) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=latin1;
31	
32SET foreign_key_checks = 0;
33	
34SET foreign_key_checks = 1;
35	
36select distinct table_name, 
37       column_name, 
38       constraint_name,  
39       referenced_table_name, 
40       referenced_column_name 
41from   key_column_usage 
42where  constraint_schema = 'XXX' 
43   and referenced_table_name is not null 
44   and referenced_column_name is not null;
45	
46set group_concat_max_len = 2048;
47set @table_name = "YourTableName";
48set @change = "bigint unsigned";
49select distinct table_name,
50       column_name,
51       constraint_name,
52       referenced_table_name,
53       referenced_column_name,
54       CONCAT(
55           GROUP_CONCAT('ALTER TABLE ',table_name,' DROP FOREIGN KEY ',constraint_name SEPARATOR ';'),
56           ';',
57           GROUP_CONCAT('ALTER TABLE `',table_name,'` CHANGE `',column_name,'` `',column_name,'` ',@change SEPARATOR ';'),
58           ';',
59           CONCAT('ALTER TABLE `',@table_name,'` CHANGE `id` `id` ',@change),
60           ';',
61           GROUP_CONCAT('ALTER TABLE `',table_name,'` ADD CONSTRAINT `',constraint_name,'` FOREIGN KEY(',column_name,') REFERENCES ',referenced_table_name,'(',referenced_column_name,')' SEPARATOR ';')
62       ) as query
63from   key_column_usage
64where  referenced_table_name is not null
65   and referenced_column_name is not null
66   and referenced_table_name = @table_name
67group by referenced_table_name