Sfrgttk

How is simplicity better than precision and clarity in prose?

Sort list of array linked objects by keys and values

What information about me do stores get via my credit card?

Would it be possible to rearrange a dragon's flight muscle to somewhat circumvent the square-cube law?

Does Parliament hold absolute power in the UK?

Finding degree of a finite field extension

How do you keep chess fun when your opponent constantly beats you?

Can withdrawing asylum be illegal?

Do working physicists consider Newtonian mechanics to be "falsified"?

How does ice melt when immersed in water

ELI5: Why do they say that Israel would have been the fourth country to land a spacecraft on the Moon and why do they call it low cost?

Change bounding box of math glyphs in LuaTeX

When did F become S in typeography, and why?

system call string length limit

Can a novice safely splice in wire to lengthen 5V charging cable?

Why can't devices on different VLANs, but on the same subnet, communicate?

Can smartphones with the same camera sensor have different image quality?

Why is the object placed in the middle of the sentence here?

How does this infinite series simplify to an integral?

How to politely respond to generic emails requesting a PhD/job in my lab? Without wasting too much time

Can a 1st-level character have an ability score above 18?

The variadic template constructor of my class cannot modify my class members, why is that so?

Did the UK government pay "millions and millions of dollars" to try to snag Julian Assange?

Would an alien lifeform be able to achieve space travel if lacking in vision?

How can I backup/dump MYSQL database with foreign key constraints?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

3

I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship.

I am taking the dump with following command:

  mysqldump -u root -p --no-data --compact db_doctor > db_doctor_no_data.sql

What am I missing?

mysql mysqldump import

share|improve this question

edited Feb 20 '14 at 12:19

dezso

22.6k116097

asked Feb 19 '14 at 14:50

Nikunj ShuklaNikunj Shukla

4837

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do the tables actually have foreign key constraints?
  
  – ypercubeᵀᴹ
  Feb 19 '14 at 14:53
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ypercube yes, It's having foreign key. While creating table I haven't include foreign key but later I altered tables to add foreign key.
  
  – Nikunj Shukla
  Feb 19 '14 at 14:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you check the output of SHOW CREATE TABLE tablename;, just to be sure? MyISAM tables silently ignore FK definitions.
  
  – ypercubeᵀᴹ
  Feb 19 '14 at 15:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ypercube : I mentioned output below. The "Key" info is for indexing(I am not sure). there is no ref key info. mysql>show create table db_doctor.db_provinces; db_provinces|CREATE TABLE db_provinces (province_id int(255) NOT NULL AUTO_INCREMENT, province_cid int(255) NOT NULL, province_name varchar(50) NOT NULL, country_abr varchar(50) NOT NULL, PRIMARY KEY (province_id), KEY province_cid (province_cid), KEY province_name (province_name), KEY fk_country_abr (country_abr) ) ENGINE=MyISAM AUTO_INCREMENT=1399 DEFAULT CHARSET=utf8 COMMENT=''
  
  – Nikunj Shukla
  Feb 19 '14 at 17:45
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  There you go. Your table is MyISAM and MyISAM tables cannot have FOREIGN KEY constraints.
  
  – ypercubeᵀᴹ
  Feb 19 '14 at 19:20
  

  
  
  
  

 | 
show 2 more comments

3

I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship.

I am taking the dump with following command:

  mysqldump -u root -p --no-data --compact db_doctor > db_doctor_no_data.sql

What am I missing?

mysql mysqldump import

share|improve this question

edited Feb 20 '14 at 12:19

dezso

22.6k116097

asked Feb 19 '14 at 14:50

Nikunj ShuklaNikunj Shukla

4837

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do the tables actually have foreign key constraints?
  
  – ypercubeᵀᴹ
  Feb 19 '14 at 14:53
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ypercube yes, It's having foreign key. While creating table I haven't include foreign key but later I altered tables to add foreign key.
  
  – Nikunj Shukla
  Feb 19 '14 at 14:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you check the output of SHOW CREATE TABLE tablename;, just to be sure? MyISAM tables silently ignore FK definitions.
  
  – ypercubeᵀᴹ
  Feb 19 '14 at 15:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ypercube : I mentioned output below. The "Key" info is for indexing(I am not sure). there is no ref key info. mysql>show create table db_doctor.db_provinces; db_provinces|CREATE TABLE db_provinces (province_id int(255) NOT NULL AUTO_INCREMENT, province_cid int(255) NOT NULL, province_name varchar(50) NOT NULL, country_abr varchar(50) NOT NULL, PRIMARY KEY (province_id), KEY province_cid (province_cid), KEY province_name (province_name), KEY fk_country_abr (country_abr) ) ENGINE=MyISAM AUTO_INCREMENT=1399 DEFAULT CHARSET=utf8 COMMENT=''
  
  – Nikunj Shukla
  Feb 19 '14 at 17:45
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  There you go. Your table is MyISAM and MyISAM tables cannot have FOREIGN KEY constraints.
  
  – ypercubeᵀᴹ
  Feb 19 '14 at 19:20
  

  
  
  
  

 | 
show 2 more comments

I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship.

I am taking the dump with following command:

  mysqldump -u root -p --no-data --compact db_doctor > db_doctor_no_data.sql

What am I missing?

mysql mysqldump import

share|improve this question

edited Feb 20 '14 at 12:19

dezso

22.6k116097

asked Feb 19 '14 at 14:50

Nikunj ShuklaNikunj Shukla

4837

  mysqldump -u root -p --no-data --compact db_doctor > db_doctor_no_data.sql

share|improve this question

edited Feb 20 '14 at 12:19

dezso

22.6k116097

asked Feb 19 '14 at 14:50

Nikunj ShuklaNikunj Shukla

4837

share|improve this question

edited Feb 20 '14 at 12:19

dezso

22.6k116097

asked Feb 19 '14 at 14:50

Nikunj ShuklaNikunj Shukla

4837

edited Feb 20 '14 at 12:19

dezso

22.6k116097

edited Feb 20 '14 at 12:19

dezso

22.6k116097

asked Feb 19 '14 at 14:50

Nikunj ShuklaNikunj Shukla

4837

asked Feb 19 '14 at 14:50

Nikunj ShuklaNikunj Shukla

4837

1 Answer
1

active

oldest

votes

0

When using MyISAM as the engine of your table, mysql will "ignore" the foreign key constraint and let you go on, this is for compatibility reasons throughout all mysql engines.

The recommended engine to use foreign keys is InnoDB, and honestly, for 99% of cases InnoDB is better.

You can easily change all your database to use InnoDB instead of MyISAM:

• add default_storage_engine = InnoDB to /etc/mysql/my.cnf (or equivalent)


• modify existing tables with alter table my_table engine=innodb
  


• to modify all tables you can try this more complex statement:

select concat_ws(' ', 'alter table', t.table_name, 'engine=innodb')
from information_schema.tables t
where t.table_type = 'base table'
and t.table_schema = 'my_db'
and t.engine != 'innodb'


Now all your foreign keys should be respected and when you use mysqldump you will se them.

share|improve this answer

answered Dec 21 '17 at 19:31

santiago ariztisantiago arizti

1413

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f59195%2fhow-can-i-backup-dump-mysql-database-with-foreign-key-constraints%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

When using MyISAM as the engine of your table, mysql will "ignore" the foreign key constraint and let you go on, this is for compatibility reasons throughout all mysql engines.

The recommended engine to use foreign keys is InnoDB, and honestly, for 99% of cases InnoDB is better.

You can easily change all your database to use InnoDB instead of MyISAM:

• add default_storage_engine = InnoDB to /etc/mysql/my.cnf (or equivalent)


• modify existing tables with alter table my_table engine=innodb
  


• to modify all tables you can try this more complex statement:

select concat_ws(' ', 'alter table', t.table_name, 'engine=innodb')
from information_schema.tables t
where t.table_type = 'base table'
and t.table_schema = 'my_db'
and t.engine != 'innodb'


Now all your foreign keys should be respected and when you use mysqldump you will se them.

share|improve this answer

answered Dec 21 '17 at 19:31

santiago ariztisantiago arizti

1413

add a comment | 

0

When using MyISAM as the engine of your table, mysql will "ignore" the foreign key constraint and let you go on, this is for compatibility reasons throughout all mysql engines.

The recommended engine to use foreign keys is InnoDB, and honestly, for 99% of cases InnoDB is better.

You can easily change all your database to use InnoDB instead of MyISAM:

• add default_storage_engine = InnoDB to /etc/mysql/my.cnf (or equivalent)


• modify existing tables with alter table my_table engine=innodb
  


• to modify all tables you can try this more complex statement:

select concat_ws(' ', 'alter table', t.table_name, 'engine=innodb')
from information_schema.tables t
where t.table_type = 'base table'
and t.table_schema = 'my_db'
and t.engine != 'innodb'


Now all your foreign keys should be respected and when you use mysqldump you will se them.

share|improve this answer

answered Dec 21 '17 at 19:31

santiago ariztisantiago arizti

1413

add a comment | 

When using MyISAM as the engine of your table, mysql will "ignore" the foreign key constraint and let you go on, this is for compatibility reasons throughout all mysql engines.

The recommended engine to use foreign keys is InnoDB, and honestly, for 99% of cases InnoDB is better.

You can easily change all your database to use InnoDB instead of MyISAM:

• add default_storage_engine = InnoDB to /etc/mysql/my.cnf (or equivalent)


• modify existing tables with alter table my_table engine=innodb
  


• to modify all tables you can try this more complex statement:

select concat_ws(' ', 'alter table', t.table_name, 'engine=innodb')
from information_schema.tables t
where t.table_type = 'base table'
and t.table_schema = 'my_db'
and t.engine != 'innodb'


Now all your foreign keys should be respected and when you use mysqldump you will se them.

share|improve this answer

answered Dec 21 '17 at 19:31

santiago ariztisantiago arizti

1413

share|improve this answer

answered Dec 21 '17 at 19:31

santiago ariztisantiago arizti

1413

answered Dec 21 '17 at 19:31

santiago ariztisantiago arizti

1413

answered Dec 21 '17 at 19:31

santiago ariztisantiago arizti

1413