SCM

Forum: bugs

Monitor Forum | Start New Thread Start New Thread
Multiple field foreign key problem [ reply ]
By: Zakariia Dev on 2008-06-11 14:30
[forum:1003610]
I'm migrating from MySQL server 4.1.7-standard to PostgreSQL 8.2.6

One of the tables is not being translated correctly. Specifically, the line:

CONSTRAINT `FK_CabinetScriptAgent_2` FOREIGN KEY (`ScriptName`, `ScriptDate`) REFERENCES `CabinetScript` (`ScriptName`, `ScriptDate`) ON DELETE CASCADE ON UPDATE CASCADE

MySQL dump:
-----------

--
-- Table structure for table `CabinetScriptAgent`
--

DROP TABLE IF EXISTS `CabinetScriptAgent`;
CREATE TABLE `CabinetScriptAgent` (
`ScriptName` varchar(45) NOT NULL default '',
`ScriptDate` datetime NOT NULL default '0000-00-00 00:00:00',
`AgentID` varchar(45) NOT NULL default '',
`Status` varchar(45) NOT NULL default '',
`Comment` varchar(45) NOT NULL default '',
`Modified` enum('true','false') NOT NULL default 'false',
PRIMARY KEY (`ScriptName`,`ScriptDate`,`AgentID`),
KEY `FK_CabinetScriptAgent_1` (`AgentID`),
CONSTRAINT `FK_CabinetScriptAgent_1` FOREIGN KEY (`AgentID`) REFERENCES `cabinetagent` (`CabinetObjectID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CabinetScriptAgent_2` FOREIGN KEY (`ScriptName`, `ScriptDate`) REFERENCES `CabinetScript` (`ScriptName`, `ScriptDate`) ON DELETE CASCADE ON UPDATE CASCADE
);


PG dump:
--------

--
-- Generated from mysql2pgsql.perl
-- http://gborg.postgresql.org/project/mysql2psql/
-- (c) 2001 - 2007 Jose M. Duarte, Joseph Speigle
--

-- warnings are printed for drop tables if they do not exist
-- please see http://archives.postgresql.org/pgsql-novice/2004-10/msg00158.php

-- ##############################################################
--
-- Table structure for table CabinetScriptAgent
--

DROP TABLE "cabinetscriptagent" CASCADE\g
CREATE TABLE "cabinetscriptagent" (
"scriptname" varchar(45) NOT NULL default '',
"scriptdate" timestamp without time zone NOT NULL default '1970-01-01 00:00:00',
"agentid" varchar(45) NOT NULL default '',
"status" varchar(45) NOT NULL default '',
"comment" varchar(45) NOT NULL default '',
"modified" varchar CHECK ("modified" IN ( 'true','false' )) NOT NULL default 'false',
primary key ("scriptname", "scriptdate", "agentid"),
"constraint" FK_CabinetScriptAgent_2 FOREIGN KEY (ScriptName, ScriptDate) REFERENCES CabinetScript (ScriptName, ScriptDate) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "cabinetscriptagent_agentid_idx" ON "cabinetscriptagent" USING btree ("agentid");
ALTER TABLE "cabinetscriptagent" ADD FOREIGN KEY ("agentid") REFERENCES "cabinetagent" ("cabinetobjectid");



When I change the foreign key to be referencing one field, like this:


--
-- Table structure for table `CabinetScriptAgent`
--

DROP TABLE IF EXISTS `CabinetScriptAgent`;
CREATE TABLE `CabinetScriptAgent` (
`ScriptName` varchar(45) NOT NULL default '',
`ScriptDate` datetime NOT NULL default '0000-00-00 00:00:00',
`AgentID` varchar(45) NOT NULL default '',
`Status` varchar(45) NOT NULL default '',
`Comment` varchar(45) NOT NULL default '',
`Modified` enum('true','false') NOT NULL default 'false',
PRIMARY KEY (`ScriptName`,`ScriptDate`,`AgentID`),
KEY `FK_CabinetScriptAgent_1` (`AgentID`),
CONSTRAINT `FK_CabinetScriptAgent_1` FOREIGN KEY (`AgentID`) REFERENCES `cabinetagent` (`CabinetObjectID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CabinetScriptAgent_2` FOREIGN KEY (`ScriptName`) REFERENCES `CabinetScript` (`ScriptName`) ON DELETE CASCADE ON UPDATE CASCADE
);


The output became:


--
-- Generated from mysql2pgsql.perl
-- http://gborg.postgresql.org/project/mysql2psql/
-- (c) 2001 - 2007 Jose M. Duarte, Joseph Speigle
--

-- warnings are printed for drop tables if they do not exist
-- please see http://archives.postgresql.org/pgsql-novice/2004-10/msg00158.php

-- ##############################################################
--
-- Table structure for table CabinetScriptAgent
--

DROP TABLE "cabinetscriptagent" CASCADE\g
CREATE TABLE "cabinetscriptagent" (
"scriptname" varchar(45) NOT NULL default '',
"scriptdate" timestamp without time zone NOT NULL default '1970-01-01 00:00:00',
"agentid" varchar(45) NOT NULL default '',
"status" varchar(45) NOT NULL default '',
"comment" varchar(45) NOT NULL default '',
"modified" varchar CHECK ("modified" IN ( 'true','false' )) NOT NULL default 'false',
primary key ("scriptname", "scriptdate", "agentid")
);
CREATE INDEX "cabinetscriptagent_agentid_idx" ON "cabinetscriptagent" USING btree ("agentid");
ALTER TABLE "cabinetscriptagent" ADD FOREIGN KEY ("agentid") REFERENCES "cabinetagent" ("cabinetobjectid");
ALTER TABLE "cabinetscriptagent" ADD FOREIGN KEY ("scriptname") REFERENCES "cabinetscript" ("scriptname");


Do you think there is a workaround I can use to fix this?

Thanks.

Powered By FusionForge