Doctrine not generating cross-database foreign key

Question

I have two tables, db1.Contact and db2.Recipient. Every recipient should be a contact so I have a foreign key set up between the two tables on the db1.Contact.ContactID field.

I represent this in Recipient.php with the following annotation:

/**
 * @ORM\Id
 * @ORM\ManyToOne(targetEntity="\db1\Contact")
 * @ORM\JoinColumn(name="ContactID", referencedColumnName="ContactID")
 **/
private $Contact;

I shouldn't need any code in the Contact.php for this.

When I generate the database (using doctrine orm:schema-tool:create --dump-sql) I can see that the Recipient.ContactID field is created and given an index. However, no query is generated and executed to create the foreign key constraint. So to be clear, I don't get any errors but the constraint is never generated.

How can I resolve this? Surely doctrine supports cross-database foreign keys? I've checked out the annotation documentation and looked to see if there are any options for the create tool but I can't see any way to turn this functionality on.

Answer 1

Doctrine doesn't support cross-DB FK:

In Doctrine, joining data across databases is not technically “supported” by a designed feature, but you can make it work by tricking Doctrine a little bit.

Source: http://www.doctrine-project.org/2009/06/19/cross-database-joins.html

Answer 2

Your ManyToOne association seems wrong. You should use the FQCN of the entities if your codebase is namespaced (or just classname if entities of both side located in same directory), not table names.

For example

/**
 * @ORM\Id
 * @ORM\ManyToOne(targetEntity="YourNamespace\Entities\Contact")
 * @ORM\JoinColumn(name="ContactID", referencedColumnName="ContactID")
 **/
private $contact;

You may want to read more about association mapping here.

Answer 3

I found a workaround for this that I explain here. Essentially, Doctrine strips cross-database foreign keys by default because not all database systems support it, but you can disable it by commenting out some code in the Doctrine library.