create indexes and foriegn keys on mysql db that uses the rails naming method

create_foriegn_keys.sql

#drop all indexes in current db that are not primary keys
select distinct concat('drop index ', index_name, ' ON ', table_name, ';') 
from information_schema.statistics
where table_schema=DATABASE() 
and index_name != 'PRIMARY'
and column_name like '%_id';

#create indexes
select concat('create index idx_',column_name, ' on ', table_name,'(',column_name,');')
from information_schema.columns
where table_schema=DATABASE() 
and column_name like '%_id';

#create foriegn keys.. (mostly works the pluralize doesn't reall work so you'll have to hand tune some of them
select concat('ALTER TABLE ',table_name, ' ADD CONSTRAINT FOREIGN KEY idx_',column_name, '(',column_name,')', ' REFERENCES ', replace(column_name, '_id','s'), '(id);')
from information_schema.columns
where table_schema=DATABASE() 
and column_name like '%_id';

#show innodb status
#use that to get useful error messages on foreign key creation. Scan down through the results until you see something like 
#------------------------
#LATEST FOREIGN KEY ERROR
#------------------------
#110908 12:30:45 Error in foreign key constraint of table....