2

I get that foreign key constraints great for integrity of a database and all, but it's also a huge overhead to use constraints when dealing with tables that are in the millions and growing.

I want to remove foreign key constraints from my application. In past symfony2 projects I've removed constraints manually, but I'm assuming maybe there is a way to simply tell symfony2 to do this?

If anyone is aware of a way to do this within the framework please let me know :)

edit:

Let's say for example in a manyToMany relationship, it auto-creates the relationship table with the proper indexes but it also puts a foreign key constraint on both columns as well, or if i have a oneToMany relationship it puts a foreign key constraint on that relationship.

I don't want these foreign key constraints to be created.

skrilled
  • 5,350
  • 2
  • 26
  • 48
  • Could you be more specific? Not really sure what you're asking – Jason Roman Dec 12 '14 at 06:11
  • edited for clarification – skrilled Dec 12 '14 at 18:29
  • I see what you're saying. Are you creating the database and then generating the entities from that? If so, just leave off the foreign key constraints and your entities will just generate with integers instead of full entities. – Jason Roman Dec 12 '14 at 18:37
  • I am creating yml files with database schema then using `app/console doctrine:schema:update --force` command to create/manage the database schema. – skrilled Dec 12 '14 at 19:03
  • Couldn't you just leave off the relations in the yml file then? – Jason Roman Dec 12 '14 at 22:05
  • 1
    Then it wouldn't pull relationships that exist though. I need the framework to grab the relationships without using a foreign key constraint on the database. Having no foreign key constraint does not jeopardize how the code fetches relationship data, so if I delete these constraints manually it continues to work fine. I.e. if I do `$object->getOtherObjects();` the relationship must be there for it to do this natively but not the constraint. – skrilled Dec 12 '14 at 22:29
  • Why not just delete the constraints after then? If you're generating the database often you could just run that in a symfony command and then add the manual deletions after? – Jason Roman Dec 13 '14 at 18:13
  • 1
    That's what I'm currently doing - however it means that going forward I can no longer rely on the framework to manage my database, which is a nifty feature that saves tons of my time. There really should be an option in the framework to disable foreign key constraints though, and I was hoping maybe someone else has been down this road and has an easy method. – skrilled Dec 16 '14 at 19:24
  • I understand where you're coming from with that. I imagine you could do some class override for specific cases and write it yourself, but that also might be something to suggest to the Doctrine devs. – Jason Roman Dec 16 '14 at 19:35
  • Figured it out via searching through their existing list of issues - luckily there was one ticket where they clarified. Thank you for your help :) – skrilled Dec 16 '14 at 20:22
  • possible duplicate of [Doctrine 2 association without foreign key constraints](http://stackoverflow.com/questions/10063792/doctrine-2-association-without-foreign-key-constraints) – flu Feb 17 '15 at 16:14

1 Answers1

2

Found in Doctrine's JIRA:

You can disable the exporting of foreign keys for specific models:

User:
  attributes:
    export: tables
  columns:

or with php:

$userTable->setAttribute(Doctrine::ATTR_EXPORT, Doctrine::EXPORT_TABLES);

Now it will only export the table definition and none of the foreign keys. You can use: none, tables, constraints, plugins, or all.

skrilled
  • 5,350
  • 2
  • 26
  • 48
  • 1
    Very nice. Can you define it for specific fields? I'm wondering because we have a database where every table has an audit column that connets to a user (like created_by_user) but we don't want the foreign key restriction on it with the extra overhead. – Jason Roman Dec 16 '14 at 20:41
  • There's no such constant `Doctrine:ATTR_EXPORT` in Doctrine2. What you're refering to is [this ticket for Doctrine 1.2](http://www.doctrine-project.org/jira/browse/DC-297) which isn't related to Doctrine2. How did you get this to work in a Symfony2/Doctrine2 project? – flu Feb 17 '15 at 16:08
  • I used the yml method, but ya essentially copied the concept from the ticket – skrilled Feb 19 '15 at 23:21
  • This looks great thank you - although if that's for Doctrine 1.2 and Symfony2...does it still apply for the latest versions? Symfony3 and Doctrine2.5 – Bendy Jun 03 '16 at 08:04