Uploaded image for project: 'Doctrine 2 - ORM'
  1. Doctrine 2 - ORM
  2. DDC-1628

onUpdate parameter on @JoinColumn not supported

    Details

    • Type: Task
    • Status: Closed
    • Priority: Major
    • Resolution: Invalid
    • Affects Version/s: 2.2
    • Fix Version/s: None
    • Component/s: Mapping Drivers
    • Labels:
      None

      Description

      It looks like this is in the older documentation (2.0) but not mentioned in the latest.

      I need to use ON UPDATE CASCADE in a few cases. Seems odd to support onDelete and not onUpdate. Am I missing something?

        Activity

        Hide
        mrthehud James Hudson added a comment -

        Sorry to drag this up again, but…

        I've also come across a use case as I'm migrating data in MySQL. I had to migrate data from a single table to MTI, which meant I had to investigate INFORMATION_SCHEMA, find the relevant constraints and change them in the migration script before I could migrate my data.

        I appreciate that migrations and emails/usernames as PKs, are probably the only use cases, and that in general you wouldn't want the onUpdate to cascade... but is there some sort of extension that can update the constraints of all tables referencing the PK of a specified table to allow the onUpdate? If not, is that possible? Perhaps this should be raised against the doctrine migrations project... I'm not sure and would value some steer.

        For anyone wondering, here's how I found the relevant constraints to batch update the onUpdate behaviour during migrations in mysql:
        ```sql
        SELECT a.CONSTRAINT_SCHEMA, a.CONSTRAINT_NAME, a.TABLE_NAME, a.COLUMN_NAME, a.REFERENCED_TABLE_NAME, a.REFERENCED_COLUMN_NAME, c.UPDATE_RULE, c.DELETE_RULE FROM KEY_COLUMN_USAGE a LEFT JOIN REFERENTIAL_CONSTRAINTS c ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND a.CONSTRAINT_SCHEMA=c.CONSTRAINT_SCHEMA WHERE a.CONSTRAINT_SCHEMA = '<db-name>' AND a.REFERENCED_TABLE_NAME='<table-name>'
        ```
        Then repeat the following for each constraint whose UPDATE_RULE is RESTRICT:
        ```sql
        ALTER TABLE <table-name> DROP FOREIGN KEY `<constraint-name>`
        ALTER TABLE <table-name> ADD CONSTRAINT `<constraint-name>` FOREIGN KEY (`<column-name>`) REFERENCES `<referenced-table-name>` (`<referenced-column-name>`) ON UPDATE CASCADE ON DELETE CASCADE");
        ```

        Show
        mrthehud James Hudson added a comment - Sorry to drag this up again, but… I've also come across a use case as I'm migrating data in MySQL. I had to migrate data from a single table to MTI, which meant I had to investigate INFORMATION_SCHEMA, find the relevant constraints and change them in the migration script before I could migrate my data. I appreciate that migrations and emails/usernames as PKs, are probably the only use cases, and that in general you wouldn't want the onUpdate to cascade... but is there some sort of extension that can update the constraints of all tables referencing the PK of a specified table to allow the onUpdate? If not, is that possible? Perhaps this should be raised against the doctrine migrations project... I'm not sure and would value some steer. For anyone wondering, here's how I found the relevant constraints to batch update the onUpdate behaviour during migrations in mysql: ```sql SELECT a.CONSTRAINT_SCHEMA, a.CONSTRAINT_NAME, a.TABLE_NAME, a.COLUMN_NAME, a.REFERENCED_TABLE_NAME, a.REFERENCED_COLUMN_NAME, c.UPDATE_RULE, c.DELETE_RULE FROM KEY_COLUMN_USAGE a LEFT JOIN REFERENTIAL_CONSTRAINTS c ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND a.CONSTRAINT_SCHEMA=c.CONSTRAINT_SCHEMA WHERE a.CONSTRAINT_SCHEMA = '<db-name>' AND a.REFERENCED_TABLE_NAME='<table-name>' ``` Then repeat the following for each constraint whose UPDATE_RULE is RESTRICT: ```sql ALTER TABLE <table-name> DROP FOREIGN KEY `<constraint-name>` ALTER TABLE <table-name> ADD CONSTRAINT `<constraint-name>` FOREIGN KEY (`<column-name>`) REFERENCES `<referenced-table-name>` (`<referenced-column-name>`) ON UPDATE CASCADE ON DELETE CASCADE"); ```
        Hide
        ibo_s I. S. added a comment -

        I have a good use case and I am really missing the onUpdate cascade.
        However it is working inside a many-to-one association but not in a one-to-one association.
        The use case is a little complicated but I can send it to you if it could change something.

        Show
        ibo_s I. S. added a comment - I have a good use case and I am really missing the onUpdate cascade. However it is working inside a many-to-one association but not in a one-to-one association. The use case is a little complicated but I can send it to you if it could change something.
        Hide
        garygolden Gary Golden added a comment -

        I have a third party table which holds users:

        CREATE TABLE `user`
        (
        `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `email` VARCHAR(255) NOT NULL UNIQUE,
        ) ENGINE=InnoDB CHARSET="utf8";

        In my table I want to use natural foreign key, so I reference `email`.

        CREATE TABLE `transaction` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `user_email` VARCHAR(255) NOT NULL,
        FOREIGN KEY (`user_email`) REFERENCES `user`(`email`)
        );

        I would like to RDBMS handle email updates on the foreign records.

        That is a real-life use case of the onDelete, which you decided to remove.
        Please, get it back if possible.

        Show
        garygolden Gary Golden added a comment - I have a third party table which holds users: CREATE TABLE `user` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `email` VARCHAR(255) NOT NULL UNIQUE, ) ENGINE=InnoDB CHARSET="utf8"; In my table I want to use natural foreign key, so I reference `email`. CREATE TABLE `transaction` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `user_email` VARCHAR(255) NOT NULL, FOREIGN KEY (`user_email`) REFERENCES `user`(`email`) ); I would like to RDBMS handle email updates on the foreign records. That is a real-life use case of the onDelete, which you decided to remove. Please, get it back if possible.
        Hide
        ocramius Marco Pivetta added a comment -

        This issue is invalid, since data migration is not a task for the ORM anyway.

        Show
        ocramius Marco Pivetta added a comment - This issue is invalid, since data migration is not a task for the ORM anyway.
        Hide
        drevolution Václav Novotný added a comment -

        +1 - onUpdate attribute is very useful for PostgreSQL databases, it controls operations with foreign keys.

        I'm taking my words back. It is not a good idea to define onUpdate on foreign keys. Yes, PostgreSQL supports it but it doesn't mean that we should use it in ORM.
        Thanks for your time.

        Show
        drevolution Václav Novotný added a comment - +1 - onUpdate attribute is very useful for PostgreSQL databases, it controls operations with foreign keys. I'm taking my words back. It is not a good idea to define onUpdate on foreign keys. Yes, PostgreSQL supports it but it doesn't mean that we should use it in ORM. Thanks for your time.

          People

          • Assignee:
            ocramius Marco Pivetta
            Reporter:
            chrisrichard Chris Richard
          • Votes:
            3 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: