incorrectly - Eliminar todas las claves externas en la base de datos(MySql)

on delete set null mysql

Question

Me gustaría cambiar el nombre de una columna en una tabla que es una clave externa para muchas tablas. Aparentemente, esto solo es posible si elimina las restricciones, como descubrí en este link .

No quiero eliminar todas las interpretaciones manualmente. ¿Existe alguna forma de eliminar todas las restricciones de clave externa en la base de datos?

También he intentado SET FOREIGN_KEY_CHECKS=0; pero todavía no puedo cambiar el nombre de la columna.

Answer 1

Ejecutando la siguiente consulta

select * from information_schema.key_column_usage

le mostrará todas las restricciones (con el nombre de la columna, el tipo de restricción, la tabla y el esquema) que existen en su base de datos. Notarás estas columnas:

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME

Luego, si planea eliminar cada restricción que hace referencia a su columna, debe considerar las columnas REFERENCED_ * y ejecutar algo como:

DELETE FROM information_schema.key_column_usage
WHERE
REFERENCED_TABLE_SCHEMA=''myschema''
AND
REFERENCED_TABLE_NAME=''mytable''
AND
REFERENCED_COLUMN_NAME=''mycolumn''

http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

Answer 2

La siguiente consulta construirá la sintaxis correcta automáticamente. Asegúrese de poner su nombre de esquema DB real en la condición WHERE . Simplemente ejecute cada línea devuelta y todas sus FOREIGN KEYS habrán desaparecido.

Dejo lo contrario (agregándolos de nuevo) como ejercicio para ti.

SELECT CONCAT("alter table ", TABLE_NAME," drop foreign key ", CONSTRAINT_NAME,"; ") AS runMe
FROM information_schema.key_column_usage
WHERE TABLE_SCHEMA=''MY_SCHEMA_NAME'' AND CONSTRAINT_NAME <> ''PRIMARY'';

Si necesita que sea un esquema independiente, puede escribir: TABLE_SCHEMA=DATABASE() para obtener el nombre de la base de datos activa actual

Answer 3

Puedes intentar usar como Me gusta de lo siguiente ...

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName);

También puedes probar con la clave de referencia. Como .....

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName)
REFERENCES anotherTableName(reference_id);

Answer 4

Puedes usar este SQL para generar ALTER TABLES ( !!YOUR_SCHEMA_HERE!! debe ser reemplazado por tu esquema):

SELECT concat(''alter table '',table_schema,''.'',table_name,'' DROP FOREIGN KEY '',constraint_name,'';'')
FROM information_schema.table_constraints
WHERE constraint_type=''FOREIGN KEY''
AND table_schema=''!!YOUR_SCHEMA_HERE!!'';

Se generará SQL de esta manera:

alter table viewpoint_test.answer_code DROP FOREIGN KEY fk_answer_code_codebook_item1;
alter table viewpoint_test.answer_code DROP FOREIGN KEY fk_answer_code_questionary_answer1;
alter table viewpoint_test.codebook DROP FOREIGN KEY codebook_ibfk_1;
...

Answer 5

Seguía teniendo algunos problemas después de encontrar este hilo, pero parece que he encontrado un flujo de trabajo que funciona para mí.

Eliminación de la base de datos

Si está utilizando MySQL Workbench, deberá desactivar la actualización segura haciendo los siguientes 3 pasos:

1. MySql Workbench> Preferencias> SQL EDITOR> (parte inferior de la configuración)
2. desactivar la ACTUALIZACIÓN SEGURA
3. Luego salga de su conexión y vuelva a conectarse

Pasos para comenzar a purgar la base de datos y comenzar de nuevo.

1. Borrar todos los datos de las tablas

(Este script MySQL creará un script DELETE FROM para cada tabla en su base de datos).

SELECT concat(''DELETE FROM '',table_schema,''.'',table_name,'';'') FROM information_schema.table_constraints WHERE table_schema=''!!TABLE_SCHEMA!!'';

Copia la salida de este y ejecútalo. Es posible que necesite esta línea por línea.

2. Eliminar todas las claves externas de sus tablas

(Este script MySql creará un script ALTER TABLE _ DROP FOREIGN KEY para cada tabla en su base de datos).

SELECT concat(''alter table '',table_schema,''.'',table_name,'' DROP FOREIGN KEY '',constraint_name,'';'') FROM information_schema.table_constraints WHERE constraint_type=''FOREIGN KEY'' AND table_schema=''!!TABLE_SCHEMA!!'';

Copia la salida de este y ejecútalo. Es posible que necesite esta línea por línea.

Después de hacer esto, DEBE poder ejecutar el script DROP DATABASE.