propelorm/Propel — Issue #191: ForeignSchema within propel-gen for MySQL
Author: iBeb
Created: 2011-11-17 12:15:12+00:00
State: open

I have two databases and some tables have foreign keys across the two databases.
From what I've read, I thought I could generate a schema.xml referencing foreign keys from a different database, but I couldn't make it work.

Here's the SQL code for the creation one one table with an external foreign key (I've kept only major columns):

CREATE TABLE `user` (
`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`group_id` int(11) unsigned NOT NULL,
`contact_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`user_id`),
KEY `u_group_id` (`group_id`),
KEY `u_contact_id` (`contact_id`),
CONSTRAINT `u_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `dream`.`contact` (`contact_id`),
CONSTRAINT `u_group_id` FOREIGN KEY (`group_id`) REFERENCES `group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Reading the MysqlSchemaParser.php, I've noticed that the regex

$regEx = '/CONSTRAINT `([^`]+)` FOREIGN KEY \((.+)\) REFERENCES `([^`]*)` \((.+)\)(.*)/';

works perfectly for tables within the same schema but excludes a reference to an external table.

I tried to find discussions about that, unsuccessfully.

I'd be glad to have an explanation about that, because I might have misunderstood something.

_bertrand