List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 8 2003 1:06am
Subject:'mysqldump' doesn't preserve REFERENCES
View as plain text  
If I create a table that uses REFERENCES in it, then mysqldump it, the
REFERENCES isn't there


Notice the `contact_dept_table_id` field in both of the below outputs:

CREATE TABLE `contact_table` (
  `contact_id` mediumint(8) unsigned NOT NULL auto_increment,
  `contact_timestamp` timestamp(14) NOT NULL,
  `contact_dept_table_id` mediumint(8) unsigned NOT NULL default '0'
REFERENCES dept_table(dept_id),
  `contact_fname` varchar(255) NOT NULL default '',
  `contact_lname` varchar(255) NOT NULL default '',
  `contact_title` varchar(50) NOT NULL default '',
  `contact_email` varchar(255) NOT NULL default '',
  `contact_incept` date NOT NULL default '0000-00-00',
  `contact_phone` varchar(20) NOT NULL default '',
  `contact_address1` varchar(70) NOT NULL default '',
  `contact_address2` varchar(70) NOT NULL default '',
  `contact_city` varchar(50) NOT NULL default '',
  `contact_state` varchar(50) NOT NULL default '',
  `contact_zip` varchar(50) NOT NULL default '',
  `contact_country` varchar(50) NOT NULL default '',
  `contact_notes` longtext NOT NULL,
  PRIMARY KEY  (`contact_id`),
  KEY `contact_dept_table_id` (`contact_dept_table_id`),
  KEY `contact_lname` (`contact_lname`),
  KEY `contact_fname` (`contact_fname`),
  FOREIGN KEY (contact_dept_table_id) REFERENCES dept_table(dept_id) ON
DELETE CASCADE
) TYPE=InnoDB;

Then execute:
[daevid=pts/1]5:01pm:{/home/daevid}> mysqldump -a crm
-- MySQL dump 8.22
--
-- Host: localhost    Database: crm
---------------------------------------------------------
-- Server version       3.23.55-Max

--
-- Table structure for table 'contact_table'
--

CREATE TABLE contact_table (
  contact_id mediumint(8) unsigned NOT NULL auto_increment,
  contact_timestamp timestamp(14) NOT NULL,
  contact_dept_table_id mediumint(8) unsigned NOT NULL default '0',  /*
shouldn't there be a REFERENCES here? */
  contact_fname varchar(255) NOT NULL default '',
  contact_lname varchar(255) NOT NULL default '',
  contact_title varchar(50) NOT NULL default '',
  contact_email varchar(255) NOT NULL default '',
  contact_incept date NOT NULL default '0000-00-00',
  contact_phone varchar(20) NOT NULL default '',
  contact_address1 varchar(70) NOT NULL default '',
  contact_address2 varchar(70) NOT NULL default '',
  contact_city varchar(50) NOT NULL default '',
  contact_state varchar(50) NOT NULL default '',
  contact_zip varchar(50) NOT NULL default '',
  contact_country varchar(50) NOT NULL default '',
  contact_notes longtext NOT NULL,
  PRIMARY KEY  (contact_id),
  KEY contact_dept_table_id (contact_dept_table_id),
  KEY contact_lname (contact_lname),
  KEY contact_fname (contact_fname),
  FOREIGN KEY (`contact_dept_table_id`) REFERENCES `crm.dept_table`
(`dept_id`) ON DELETE CASCADE
) TYPE=InnoDB;

--
-- Dumping data for table 'contact_table'
--

Thread
'mysqldump' doesn't preserve REFERENCESDaevid Vincent8 Mar
  • re: 'mysqldump' doesn't preserve REFERENCESEgor Egorov8 Mar