How to stop Doctrine2 Migrations:Diff from always adding foreign key relationships that already exist in Database?

Question

I'm using doctrine2 with a symfony2.1 project. I have an entity that has a few many to one relationships to other tables. The foreign key relationships for these many-to-one's have already been updated in the database, but every time I run migrations:diff or schema:update --dump-sql it adds the same update commands to add the foreign key relationships again. When I run schema:validate it says my mapping is out of sync with my database.

My application works fine, the relationships are working properly, and the schema in my database looks correct. Why is doctrine still trying to add these foreign keys?

Here's my code (for one of the problematic parameters):

In my "Ticket" entity I have:

/**
 * Authenticated User who scored the ticket. 
 *
 * @ORM\ManyToOne(targetEntity="CS\SecurityBundle\Entity\User")
 * @ORM\JoinColumn(name="scoring_user_id", referencedColumnName="id")
 */
protected $scoringUser;

I currently have it set up to be one-directional, so there is no inversedBy in the User entity.

This generates the following in my migrations or schema:update dump even though it's in the database already:

$this->addSql("ALTER TABLE tickets ADD CONSTRAINT FK_54469DF4BB0D9452 FOREIGN KEY (scoring_user_id) REFERENCES users (id)");

Any idea what I'm doing wrong here?

Villein

1) make sure that the database has that CONSTRAINT, 2) try to upgrade doctrine – Upstate

Thanks Meze. I think the problem is that my database isn't using INNODB so it can't set the Constraint. (if I'm understanding that correctly). Any idea of how to make doctrine not try to set the Constraint? This seems to be a similar question: #12157275 – Villein

Have you tried @Table(name="table_name",options={"engine"="MyISAM"}) or whatever engine you are using? – Diabolize

Why aren't you using InnoDB? – Compass

Answer 1

Why is doctrine still trying to add these foreign keys?

The correct term here is "foreign key constraint". Without the constraint, the column in question is just a column. It's the constraint that enforces that the value of that column exists as a primary key in another table.

Why is doctrine still trying to add these foreign keys?

Because the database vendor/engine doesn't support foreign key constraints, but Doctrine fails to recognize that.

If I have to guess, you're using MySQL with the MyISAM engine. MyISAM doesn't support foreign key constraints. Unfortunately Doctrine isn't "smart" enough to see that. It sees that MySQL is used, therefor blindly assumes that foreign key constraints are supported.

My advise is to switch to the InnoDB engine, unless you have a good reason for using MyISAM.

ALTER TABLE table_name ENGINE=InnoDB;

Converting Tables from MyISAM to InnoDB

Lectionary

This is what was happening, and yes I ended up switching to InnoDB after learning more about the benefits over MyISAM. – Villein

Sorry for commenting, but an upvote was not enough. Your answer has saved my life! Thank you – Whiffet

@Villein InnoDB indeed has many advantages over MyISAM, but there are cases where MyISAM is desirable (hence the "unless you have a good reason" in my answer). – Lectionary