Doctrine 2 association without foreign key constraints

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?

score 13 · Answer 1 · answered Jun 06 '13 at 14:02

13

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.

answered Jun 06 '13 at 14:02

Pier-Luc Gendreau

13,553
4
58
69

10

I added it to my `JoinColumn` annotation but the foreign key constraint is still being created. – flu Feb 17 '15 at 15:17
1

same as @flu - no luck, foreign key is being created anyway – Serge Velikan Apr 06 '15 at 16:04
I haven't worked with Doctrine since then, but are you sure whether it's being created or if it already exists and wasn't removed. – Pier-Luc Gendreau Apr 06 '15 at 16:08
Doesn't work for me if I am trying to generate a many-to-many relational table via command line usage of doctrine2. As an alternative, I used a query which detects all foreign keys in the database & dropped them (http://stackoverflow.com/a/27481326/1153885) – Rohit Apr 21 '15 at 06:50
yep, foreign key is being created anyway – Ashton Honnecke Dec 09 '16 at 18:44
1

to reinforce this answer - for the cases when foreign key is being created anyway and schema update fails - that is because you schema was partially updated and invalid join id's were filled in. check the join table in your db and revert by removing the added column which probably contains 0's instead of NULL's, then re-run schema update after adding nullable=true to the join column annotation. The foreign key will still be created, but it won't fail because there will be no invalid values. – Mihai MATEI Dec 31 '19 at 17:15

score 8 · Answer 2 · answered Apr 08 '12 at 15:12

8

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.

answered Apr 08 '12 at 15:12

Cerad

48,157
8
90
92

4

Yes but then I can't use the command `app/console doctrine:schema:update --force` anymore because the update stops when it tries to execute the FK constraints. – chiborg Apr 11 '12 at 13:40
This answer combined with the one from @Pier-Luc-Gendreau did the Trick for me. If you want Doctrine2 to manage your DB relationss, then you need to clean up your data from missing relations and then you can create the missing constraints. – Marcel Grolms Mar 08 '20 at 10:27

score 0 · Answer 3 · answered Jun 12 '12 at 12:41

0

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.

answered Jun 12 '12 at 12:41

Denes Papp

3,835
3
32
33

score 0 · Answer 4 · answered Jun 06 '13 at 13:03

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.

score 0 · Answer 5 · answered Nov 09 '16 at 08:07

0

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

answered Nov 09 '16 at 08:07

S.Wörner

11
1

score -2 · Answer 6 · edited Apr 06 '15 at 21:09

-2

Try to add onDelete="CASCADE" like

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

edited Apr 06 '15 at 21:09

Pier-Luc Gendreau

13,553
4
58
69

answered May 31 '14 at 08:05

nvvetal

1,756
1
17
19

Doctrine 2 association without foreign key constraints

6 Answers6

Linked