schema update with doctrine2 postgresql always DROPs and then ADDs CONSTRAINTs

Question

When updating schema, doctrine always drops and add constraints. I think, it is something wrong...

php app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "112" queries were executed

php app/console doctrine:schema:update --dump-sql
ALTER TABLE table.managers DROP CONSTRAINT FK_677E81B7A76ED395;
ALTER TABLE table.managers ADD CONSTRAINT FK_677E81B7A76ED395 FOREIGN KEY (user_id) REFERENCES table."user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
...

php app/console doctrine:schema:validate
[Mapping]  OK - The mapping files are correct.
[Database] FAIL - The database schema is not in sync with the current mapping file.

How can this may be fixed?

Answer 1

After some digging into doctrine update schema methods, I've finally found an issue. The problem was with table names - "table.order" and "table.user". When doctrine makes diff, this names become non equal, because of internal escaping (?). So, "user" != user, and foreign keys to those tables (order, user) always recreating.

Solution #1 - just rename tables to avoid name matching with postgresql keywords, like my_user, my_order. Solution #2 - manually escape table names. This not worked for me, tried many different escaping ways.

I've applied solution #1 and now I see:

Nothing to update - your database is already in sync with the current entity metadata

Answer 2

I have had the same issue on Postgres with a uniqueConstraint with a where clause.

* @ORM\Table(name="avatar",
* uniqueConstraints={
* @ORM\UniqueConstraint(name="const_name", columns={"id_user", "status"}, options={"where": "(status = 'pending')"})

Doctrine is comparing schemas from metadata and new generated schema, during indexes comparation where clauses are not matching.

string(34) "((status)::text = 'pending'::text)"
string(20) "(status = 'pending')"

You just have to change you where clause to match by

((avatar)::text = 'pending'::text)

PS: My issue was with Postgres database

I hope this will help someone.