#380 FKs not copied when copying DB

4.0.3
closed-wont-fix
nobody
5
2014-07-23
2014-02-07
Radim Pospíšil
No

Foreign keys seem no to get copied when copying entire DB to another one. When moving DB then FKs are moved as well.
E.g.: I have DB1 with FKs in some tables. Marking all tables and choosing to copy them to DB2 does not copy FKs. Whereas moving DB1 to DB2 moves FKs as well.
MySQL: 5.5.32 (PHP, MySQLi)
Adminer: 4.0.3

Discussion

  • The whole problem is more complicated but it could turn to be a quick win (see below). There are few things that MySQL omits when copying tables using CREATE TABLES ... LIKE. More info here: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

    CREATE TABLE ... LIKE does not preserve ... any foreign key definitions.

    It means FKs have to be created separately. One way is described here: http://stackoverflow.com/a/3756237/2709029

    However, much easier way would be:
    1) Export DB (all tables, triggers, ... using DROP+CREATE options),
    2) change all occurences of the string "DB1" to "DB2" - there are 3 of them right at the beginning (DROP DATABASE..., CREATE DATABASE and USE statements),
    3) execute changed SQL command.

     
    Last edit: Radim Pospíšil 2014-02-20
  •  
    Last edit: Radim Pospíšil 2014-02-20
  • Jakub Vrána
    Jakub Vrána
    2014-03-15

    • status: open --> closed-wont-fix
     
  • Jakub Vrána
    Jakub Vrána
    2014-03-15

    One of the problems is that the foreign key name must be unique per database and SHOW CREATE TABLE includes this name. The consequence is that if you try to copy table to the same database using SHOW CREATE TABLE then it fails.