1

I am using HeidiSQL because I find it much easier to build in than PMA but PMA has a nice basic diagramming tool.

I had a DB that was called test2. I wanted to rename it or ideally clone it. Sadly could not see how to make a copy in Heidi but could change the name. All fine.

But when I went into PMA all the foreign keys are gone. I have looked and cannot see if anyone has had this problem.

I went back to Heidi, renamed it test2: nothing doing. Then I stopped and restarted MySQL and voila everything fine.

I tried the PMA copy database function - copied fine but no FKs. Stopped and restarted mySQL but not there.

I have not done any database work for about a year. Once before I exported a medium size newb DB from either Heidi or PMA and remember horrors of losing days trying to get the database to reimport.

(Ideally I would love a "safe" way of cloning a mySQL DB (including FKs) in a safe manner for reinstalling on a remote server and or duplicating here. As I said my experience has been horrible.)

EDIT: In Heidi the table I copied in PMA has all it FKs but they are not there in PMA - which seems weird for lack of a more robust word!

Having said that, FKs are showing in the diagrams but while the structure view shows an index.

enter image description here

when I go to relational view I get:

enter image description here

Shouldn't this show the links to the other table???

If I use just Heidi is there a simple DB diagramming tool I can use in conjunction. I think I remember having had horrible problems with PMA before but then it does allow me to copy DBs.

halfer
  • 19,824
  • 17
  • 99
  • 186
BeNice
  • 2,165
  • 2
  • 23
  • 38
  • 1
    I saw a similar problem here: https://github.com/phpmyadmin/phpmyadmin/issues/11798 where the db name contains a dot, which is not supported by phpMyAdmin for some panels. – Marc Delisle Dec 31 '15 at 21:27
  • Marc thanks seem to have hacked around then problem by copying in Heidi (see below). Just one of the many things I think will take 5 mins but takes three hours. – BeNice Dec 31 '15 at 22:27
  • What table type are these tables? The fact that phpMyAdmin is showing the "Internal relations" dialog but not "Foreign key constraints" is strange. – Isaac Bennetch Jan 05 '16 at 02:29

1 Answers1

1

With HeidiSQL you can also copy a database:

  • create the empty target database
  • rightclick the source database, click Export database as SQL
  • select output: Database, and select the database you created in step 1
  • be sure to have tables: create checked, and data: insert
  • click Export

That export should also copy foreign keys along with all other stuff.

The fact that PMA does not show up the copied foreign keys looks like a bug in PMA to me. You could also check the foreign keys with a third MySQL client, like MySQL Query Browser.

You could also post the CREATE TABLE statements of your tables here, so I can try to reproduce that issue here.

Anse
  • 1,573
  • 12
  • 27
  • Anse thanks for that. Discovered that last night. May try and copy over stuff but have now, somehow got it working well enough and I am horribly behind. I have a note on my wall. "Is it interesting? Great! Does it help build the website... NO? Then *** ignore it and HACK the problem!!!" So I have a good enough solution and can fall back on Vertabello if need be. Thanks v much though for the clear answer. – BeNice Dec 31 '15 at 22:25