Doctrine 2 association without foreign key constra

Question

I'm in the process of converting a legacy PHP application to Symfony 2. The application data is not very consistent at the moment, so I would like to avoid creating foreign key constraints. I have the following annotation in my "Product" entity class:

class Product {

  // some definitions

  /**
   * @ORM\ManyToOne(targetEntity="Manufacturer")
   * @ORM\JoinColumn(name="manufacturer_id", referencedColumnName="id" )
   */
  private $Manufacturer;
}

When I do app/console doctrine:schema:update, I get the SQL command

 ALTER TABLE products ADD CONSTRAINT FK_F6FA18741C3BF575 
   FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id);

How can I avoid this?

Answer 1

I had to go through the same process recently and fortunately there is an easy solution, just add nullable=true to the column's annotation.

This will work as long as the existing data is valid, in my case I had to change 0's to NULL's and change keys that didn't exist anymore to NULL.

Answer 2

Basically you can't prevent the sql commands from being generated. At least not without diving into the Doctrine code.

However, you don't need to actually apply the constraints to your database. D2 will work just fine without them.

Answer 3

I had a problem with the same command. I got the exception:

SQLSTATE[HY000]: General error: 1005 Can't create table 'xxx.#sql-66c_3e' (errno: 150)

For me it helped to declare the column as unique (in your case id).

Now app/console doctrine:schema:update runs fine again.

Answer 4

Try to add onDelete="CASCADE" like

* @ORM\JoinColumn(name="manufacturer_id", referencedColumnName="id", onDelete="CASCADE")

Answer 5

You have to set lost foreign keys to null, then you can set your contstraint. Following query gives you the ids from datasets to change:

select p.id from product p
left join manufacturer m on m.id=p.manufakturer_id
where m.id is null

Answer 6

You should leave out the ManyToMany and the JoinColumn definitions and handle the Manufacturer property in your custom Product repository with a public getManufacturer method that extends the auto generated Product repository.