Hello Jerry, I can give you the shema of my "parent" table: CREATE TABLE `demo_employee` (\n `idemployee` int(10) unsigned NOT NULL auto_increment,\n `demo_mandt_idmandt` int(10) unsigned NOT NULL default '0',\n `demo_adress_idadress` int(10) unsigned NOT NULL default '0',\n `demo_adress_demo_region_idregion` int(10) unsigned NOT NULL default '0',\n `demo_adress_demo_country_idcountry` int(10) unsigned NOT NULL default '0',\n `demo_biz_role_idbiz_role` int(10) unsigned NOT NULL default '0',\n `demo_job_idjob` int(10) unsigned NOT NULL default '0',\n `demo_user_iduser` int(10) unsigned NOT NULL default '0',\n `I_EE_EMPLOYEE_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_GIVEN_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_MIDDLE_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_FAMILY_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_INITIALS_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_BANK_ACCOUNT_HOLD` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_BANK_ACCOUNT_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_PO_AD_FLO_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_PO_AD_ROOM_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_START_DATE_HIRING` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_END_DATE_HIRING` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_EVENT_TYPE_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_TYPE_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_ADMIN_CATEGORY_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_COMPANY_ID` int(10) unsigned default NULL,\n `I_PEH_POS_ASS_BEG_DATE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_POSITION_MAIN_INDICATOR` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_POS_ASSIGNMENT_PERCENT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_FIRST_VALUE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_FIRST_UNIT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_SEC_VALUE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_SEV_UNIT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_NOTICE_PERIOD` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_GENDER_CODE` int(10) unsigned default NULL,\n `I_EE_CO_MATERIAL_STATUS_CODE` int(10) unsigned default NULL,\n `I_EE_CO_FROM_OF_ADRESS_CODE` int(10) unsigned default NULL,\n `I_EE_CO_ACADEMIC_TITLE_CODE` int(10) unsigned default NULL,\n `I_EE_CO_BIRTH_PLACE_NAME` varchar(45) collate utf8_unicode_ci default NULL,\n `I_EE_CO_BIRTH_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_BIRTH_DATE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_ADDITIONAL_ACA_TITLE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_HO_ADD_USAGE_DEFAULT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_HO_ADD_USAGE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_ADD_USAGE` varchar(25) collate utf8_unicode_ci default NULL,\n `demo_user` int(10) unsigned default NULL,\n PRIMARY KEY (`idemployee`,`demo_mandt_idmandt`,`demo_adress_idadress`,`demo_adress_demo_region_idregion`,`demo_adress_demo_country_idcountry`,`demo_biz_role_idbiz_role`,`demo_job_idjob`,`demo_user_iduser`),\n KEY `demo_employee_FKIndex1` (`demo_mandt_idmandt`),\n KEY `demo_employee_FKIndex2` (`demo_adress_idadress`,`demo_adress_demo_region_idregion`,`demo_adress_demo_country_idcountry`),\n KEY `demo_employee_FKIndex3` (`demo_biz_role_idbiz_role`),\n KEY `demo_employee_FKIndex4` (`demo_job_idjob`),\n KEY `demo_employee_FKIndex5` (`demo_user_iduser`),\n CONSTRAINT `demo_employee_ibfk_1` FOREIGN KEY (`demo_mandt_idmandt`) REFERENCES `demo_mandt` (`idmandt`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `demo_employee_ibfk_2` FOREIGN KEY (`demo_adress_idadress`, `demo_adress_demo_region_idregion`, `demo_adress_demo_country_idcountry`) REFERENCES `demo_adress` (`idadress`, `demo_region_idregion`, `demo_country_idcountry`) ON DELETE NO ACTION ON UPDATE NO ACTION,\n CONSTRAINT `demo_employee_ibfk_3` FOREIGN KEY (`demo_biz_role_idbiz_role`) REFERENCES `demo_biz_role` (`idbiz_role`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `demo_employee_ibfk_4` FOREIGN KEY (`demo_job_idjob`) REFERENCES `demo_job` (`idjob`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `demo_employee_ibfk_5` FOREIGN KEY (`demo_user_iduser`) REFERENCES `demo_user` (`iduser`) ON DELETE CASCADE ON UPDATE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Know i'm searching an easy way to copy the "parent" table an all his childs but with the new key values. Currently i do this via an PHP script, but it is a lot of work and not very flexible. Dosen't provide mysql any easy function ? Kind regards -----Ursprüngliche Nachricht----- Von: Jerry Schwartz [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 22. November 2006 15:54 An: Eidner, Fabian; mysql@lists.mysql.com Betreff: RE: Copy some datasets including the foreign key constraints Do you know what your query looks like after variable substitution? That always helps me a lot. If you can't dump it to the screen because it breaks too much of your system, look into syslog(). That's what I use when debugging the heart of our online system, and it has helped me find missing single quotes and the like. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Eidner, Fabian [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 22, 2006 7:46 AM > To: mysql@lists.mysql.com > Subject: Copy some datasets including the foreign key constraints > > Hello list, > I'm pretty new here. > > But i already got an problem. I'm working a while with mysql currently > i'm trying to duplicate some entries in my tables. > The problem is, i'm using foreign key constraints between those tables > and i also would like to duplicate the childs. > > So i got one big parent, these parent table has some child tables. All > are contatenatet via the FK constraints. > Currently i'm extracting the references by foot. So i do a SHOW TABLE > STATUS FROM akron LIKE '$tablename'. > After that i explode the information an so on. Very dirty stuff, a lot > of code. > > And now after all the hard work mysql throws me this when i try to > insert a copy of a parent: Cannot add or update a child row: a foreign > key constraint fails > The childs do exist, but i think my sql syntax is wrong i use: "INSERT > INTO $non_atomar[$i] VALUES ('',$values)" > The key values are right, but i think sql need another syntax to be > happy. > > The other thing is, i can solve my problem in this dirty way, > but i hope > that there is an easyer way to do this. > Did anyone know an easy way to copy a parent table and all > his childs ? > The only thing that changes are the keys. > > Kind regards > > Fabian Eidner > > E [EMAIL PROTECTED] > > B-O-S website http://www.brotherhood-of-steel.org > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
