4

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?

  • 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
  • 1
    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
  • 2
    There you go. Your table is MyISAM and MyISAM tables cannot have FOREIGN KEY constraints. – ypercubeᵀᴹ Feb 19 '14 at 19:20
1

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.

| improve this answer | |
  • You should mention that MyISAM supports Full Text Indexing while InnoDB does not. I think its relevant – Ricardo Saracino Aug 15 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.