Bases de datos

MySQL

Eliminación y creación de llaves foráneas

Última actualización: 14-11-2017 18:26

La unión de estos dos SELECTs da como resultado el listado de comandos de eliminación y creación de todas las llaves foráneas que existan en la base de datos a la que se está conectado.


 
select XSQL
from (
    select 1 as N, concat('alter table ', TABLE_NAME, ' drop foreign key ', CONSTRAINT_NAME, ';') as XSQL 
    from information_schema.TABLE_CONSTRAINTS
    where TABLE_SCHEMA = database() and CONSTRAINT_TYPE = 'FOREIGN KEY'
    union    
    select 2, concat('alter table ', a.TABLE_NAME,
                                    ' add constraint ', a.CONSTRAINT_NAME,
                                    ' foreign key (',  b.COLS, ')',
                                    ' references ', b.REFERENCED_TABLE_NAME, ' (', b.REF_COLS, ');'
                                    ) as XSQL10
    from information_schema.TABLE_CONSTRAINTS a
			inner join (
						select b.TABLE_NAME, b.CONSTRAINT_NAME, a.REFERENCED_TABLE_NAME,
								GROUP_CONCAT(a.COLUMN_NAME ORDER BY a.ORDINAL_POSITION SEPARATOR ', ') as COLS, 
								GROUP_CONCAT(a.REFERENCED_COLUMN_NAME  ORDER BY a.ORDINAL_POSITION SEPARATOR ', ') as REF_COLS
						from information_schema.KEY_COLUMN_USAGE a
								inner join information_schema.TABLE_CONSTRAINTS b
									on b.table_schema = database()
										and a.table_schema = database()
										and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME
										and b.CONSTRAINT_TYPE = 'FOREIGN KEY'
						group by b.TABLE_NAME, b.CONSTRAINT_NAME, a.REFERENCED_TABLE_NAME
            ) b on a.TABLE_SCHEMA = database() and b.TABLE_NAME = a.TABLE_NAME and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME               
                and a.CONSTRAINT_TYPE = 'FOREIGN KEY'
) x
order by N;