List:General Discussion« Previous MessageNext Message »
From:Frank Date:April 25 2006 7:30am
Subject:Re: mysqldump and foreign keys
View as plain text  
Nico Rittner wrote:

> hi,
> 
>> are you using the InnoDB storage engine for your tables?
> yes, i do.
> 
> example:
> 
> $ mysqldump -d core groups :
> 
> CREATE TABLE `groups` (
> `id` smallint(5) unsigned NOT NULL default '0',
> `name` varchar(32) NOT NULL default '',
> `parent_id` smallint(5) unsigned default NULL,
> `setting_` text NOT NULL,
> `r__groups_users_status__id` tinyint(3) unsigned NOT NULL default '0',
> `_ctime` int(10) unsigned NOT NULL default '0',
> `_mtime` int(10) unsigned NOT NULL default '0',
> `_uid` smallint(5) unsigned NOT NULL default '0',
> `_gid` smallint(5) unsigned NOT NULL default '0',
> `_mod` smallint(5) unsigned NOT NULL default '0',
> PRIMARY KEY  (`id`),
> KEY `_uid` (`_uid`),
> KEY `_gid` (`_gid`),
> KEY `_mod` (`_mod`),
> KEY `parent_id` (`parent_id`),
> KEY `r__groups_users_status__id` (`r__groups_users_status__id`),
> CONSTRAINT `groups_ibfk_10` FOREIGN KEY (`_gid`) REFERENCES `groups`
> (`id`), CONSTRAINT `groups_ibfk_11` FOREIGN KEY (`_uid`) REFERENCES
> `users` (`id`), CONSTRAINT `groups_ibfk_7` FOREIGN KEY (`parent_id`)
> REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_8` FOREIGN KEY
> (`r__groups_users_status__id`) REFERENCES `groups_users_status` (`id`), )
> ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups';
> 
> As you can see, the foreign keys - statements are included,
> but without the 'action parts' ( on update,on delete );
> 
> thanks,
> 
> Nico

Hi Nico,

not sure what is going on here. In a simple test I did, everything was fine
as yo can see below. I've also created your groups table (without the
references to other tables) and added and "on delete" clause, which was
dumped fine. Could be a bug in 4.1.14 of course.

Sorry to ask this questions, but are you confident that the action clauses
work properly, i.e. the tables were created correctly?
What do you see when you execute "show create table groups"?


Cheers
Frank

> mysqldump -d test child
-- MySQL dump 10.9
--
-- Host: 127.0.0.1    Database: test
-- ------------------------------------------------------
-- Server version       4.1.13

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
[snip]
--
-- Table structure for table `child`
--

DROP TABLE IF EXISTS `child`;
CREATE TABLE `child` (
  `parent_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `t1` (`id`)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thread
mysqldump and foreign keysNico Rittner23 Apr
  • Re: mysqldump and foreign keysFrank24 Apr
    • Re: mysqldump and foreign keysNico Rittner24 Apr
  • Re: mysqldump and foreign keysFrank25 Apr
    • Re: mysqldump and foreign keysNico Rittner25 Apr
  • Re: mysqldump and foreign keysFrank26 Apr
    • Re: mysqldump and foreign keysNico Rittner26 Apr
  • Re: mysqldump and foreign keysFrank27 Apr