Mysql: Programmatically remove all foreign keys

Question

I'm working with a bit of a dodgey database at the moment, there are foreign keys defined in all the wrong places all over the place, I'd like to remove them all and then start from scratch. I don't wish to remove the column, just the foreign key relationships.

How can I remove all foreign keys from an entire database? (Or table by table).

Thanks.

Edit: Forgot to say, I have PHPMyAdmin available to use.

Answer 1

I would use a tool to access MySQL Metadata programatically. JDBC, ODBC, MySQL's native API or ADO.NET. From the Metadata extract all existing foreign keys. Loop through all of them and execute:

alter table INSERT_TABLE_NAME_HERE drop constraint INSERT_CONSTRAINT_NAME_HERE;

Answer 2

I hope this link might help you. http://www.futhark.ch/mysql/130.html

Answer 3

Here's a PHP script to loop through the information_schema.key_column_usage table and drop each foreign key:

<?php
$DBNAME = 'dbname';
mysql_connect('localhost', 'username', 'password');

mysql_select_db($DBNAME);
$result = mysql_query("SELECT DISTINCT table_name, constraint_name"
  . " FROM information_schema.key_column_usage"
  . " WHERE constraint_schema = '$DBNAME'"
  . " AND referenced_table_name IS NOT NULL");
while($row = mysql_fetch_assoc($result)) {
  mysql_query("ALTER TABLE `$row[table_name]`"
    . " DROP FOREIGN KEY `$row[constraint_name]`")
    or die(mysql_error());
}
mysql_free_result($result);
mysql_close();
?>