I am not an expert in this, but it looks as though you are trying to define foreign keys on your "parent" table. I thought you had to define the parent table without foreign keys, and then define a child table with foreign key constraints.
I'm also not sure if you are trying to redefine the key columns, or the key values. If you are changing the key values in the copies of your tables, I think you have to copy both the parent and the child and then update the key value in the parent. That should cascade down into the child. Perhaps I do not understand what you are doing, since you said it works when you use your PHP code. 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: Thursday, November 23, 2006 6:47 AM > To: Jerry Schwartz > Cc: mysql@lists.mysql.com > Subject: AW: Copy some datasets including the foreign key constraints > > 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`,`dem o_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`,`de mo_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]
