CODEWITHSUNDEEP
phppropel
user2038988
user2038988

Reputation: 21

Foreign key to a remote database with Propel

I have a question about foreign keys in propel. I have got 2 schemas in my project that match to 2 physical databases: my local one (DB One) and a remote DB with some read only information (DB Two)

The point is that I need to set a foreign key from DB One to DB Two, but it doesn't work. Here is my schemas:

Schema 1

<database package="dbOne" defaultIdMethod="native" name="dbOne">
  <table name="tableOne">
    <column name="pk_tableOne" type="INTEGER" primaryKey="true" required="true" autoIncrement="true"/>
    <column name="column_one" type="DOUBLE" required="true"/>
    <foreign-key name="fk_column_one" foreignTable="tableTwo" foreignSchema = "dbTwo">
      <reference local="column_one" foreign="column_two"/>
    </foreign-key>
      </table>
</database>

Schema 2

<database package="dbTwo" defaultIdMethod="native" name="dbTwo">
  <table name="tableTwo">
    <column name="column_two" type="DOUBLE" primaryKey="true" required="true"/>
</database>

I have set both runtime/build-conf.xml with both datasources, during om/diff command i got this error:

Execution of target "om-template" failed for the following reason: "tableOne" contains a foreign key to nonexistent table "dbTwo.tableTwo"

What am I doing wrong?

Upvotes: 2

Views: 1101

Answers (2)

Eelke van den Bos
Eelke van den Bos

Reputation: 1463

You cannot join on a foreign database. But: If your database supports it (You need a database like PGSQL), you can place you're tables in seperate schema's within the same database. This gives you the same seperation of your tables, but with the ability to apply joins (and setting foreign keys).

Upvotes: 0

Jordan Kasper
Jordan Kasper

Reputation: 13273

Unfortunately, I don't think you're going to be able to set up foreign key references to a table on a different database. If you look at the generator files, we see this line:

In propel/generator/lib/model/Table.php, line 901 in the current github version:

public function setupReferrers($throwErrors = false)
{
  foreach ($this->getForeignKeys() as $foreignKey) {

    // table referrers
    $foreignTable = $this->getDatabase()->getTable($foreignKey->getForeignTableName());
    if ($foreignTable !== null) {
      ...
    } elseif ($throwErrors) {
      throw new BuildException(sprintf(
        'Table "%s" contains a foreign key to nonexistent table "%s"',
        $this->getName(),
        $foreignKey->getForeignTableName()
      ));
    }
    ...
  }
  ...
}

The key line is where it says $foreignTable = $this->getDatabase()... in other words, it will only work on references to that table's own database. I would recommend adding your voice to this github issue which seems to be what you're asking for.

Upvotes: 1

Related Questions