2

I created my MySQL schema that consists of multiple tables and I decided that I would add the foreign key constraints afterwards for each table, using the command:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

How can I get a backup of the schema (containing the foreign keys) so that I can duplicate it in another machine?

Note that SHOW CREATE TABLE and mysqldump do not work in my case because they only create a UNIQUE KEY constraint and not a FOREIGN KEY.

Yiannis Tsimalis
  • 739
  • 2
  • 11
  • 23
  • mysqldump create the dump of foreign keys as well... – Aman Aggarwal May 26 '14 at 05:32
  • If `SHOW CREATE TABLE` and `mysqldump` don't show a table definition with the foreign key constraints that you think you added, the most likely explanation is that you did not add them. If the constraints were there, they would be visible. – Michael - sqlbot May 26 '14 at 12:34
  • This is what `SHOW CREATE TABLE experience` returns: `CREATE TABLE 'experience' ( 'ExperienceID' int(11) NOT NULL AUTO_INCREMENT, 'OrganizationID' int(11) NOT NULL, 'UserID' int(11) NOT NULL, 'PeriodStart' date NOT NULL PRIMARY KEY ('ExperienceID') ) ENGINE=InnoDB AUTO_INCREMENT=147023 DEFAULT CHARSET=utf8 COLLATE=utf8_bin` The UserID is a foreign key and when I check the designer in phpMyAdmin, the foreign keys are graphically shown. – Yiannis Tsimalis May 26 '14 at 14:46

2 Answers2

8

mysqldump create the dump of foreign keys as well... it adds syntax like:

mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;

You can read the manual at: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html for mysqldump of foreign keys

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
1

I come across this often. This is how I do it so I can use 'pv' from the command line to get a progress bar on the sql dump while it restores:

{ echo "SET foreign_key_checks=0;";pv sql.gz; echo "SET foreign_key_checks=1;"; } | mysql dbname

(I put the several commands in { } so that its treated like a single command when being piped to mysql while retaining the progress bar from 'pv')

noufalcep
  • 3,446
  • 15
  • 33
  • 51
Joe Huss
  • 11
  • 2
  • Very god answer, it automate all steps to load the data so I never forget to set foreing_key_checks again. And pv is a plus. – Juan Dec 20 '19 at 13:04