Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-58

Schema tool does not see difference between onDelete="NO ACTION" and onDelete="RESTRICT"

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Schema Managers
    • Labels:
      None

      Description

      If database has ON DELETE CASCADE, schema tool detects change to NO ACTION and RESCRICT.
      If database has ON DELETE RESCRICT, schema tool detects change only in case of CASCADE.
      If database has ON DELETE NO ACTION, schema tool detect change only in case of CASCADE.

      Detecting of changes is done by:
      doctrine orm:schema-tool update --dump-sql
      and
      doctrine orm:validate-schema

        Activity

        Hide
        Steve Müller added a comment -

        Jan Obrátil As there internally is no difference between NO ACTION AND RESTRICT in MySQL I am closing this now. Doctrine generates the correct statements here when necessary.

        Show
        Steve Müller added a comment - Jan Obrátil As there internally is no difference between NO ACTION AND RESTRICT in MySQL I am closing this now. Doctrine generates the correct statements here when necessary.
        Hide
        Benjamin Eberlei added a comment -

        Hm you are right, however for MySQL NO ACTION is the same as RESTRICT as stated by http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

        Show
        Benjamin Eberlei added a comment - Hm you are right, however for MySQL NO ACTION is the same as RESTRICT as stated by http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
        Hide
        Jan Obrátil added a comment -

        I have attached script with two entities.

        I have this database version:
        $ mysql --version
        mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

        Check line 44. There is CASCADE.
        Updating schema by: $ doctrine orm:schema-tool:update

        Change CASCADE to NO ACTION
        $ doctrine orm:schema-tool:update --dump-sql
        ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
        ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE NO ACTION

        everything ok, so: $ doctrine orm:schema-tool:update

        Change NO ACTION to RESTRICT
        $ doctrine orm:schema-tool:update --dump-sql
        // no output!!

        Change RESTRICT to CASCADE
        $ doctrine orm:schema-tool:update --dump-sql
        ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
        ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE CASCADE

        everything ok, so: $ doctrine orm:schema-tool:update

        Change CASCADE to RESTRICT
        $ doctrine orm:schema-tool:update --dump-sql
        ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
        ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE RESTRICT

        everything ok, do: $ doctrine orm:schema-tool:update

        Change RESTRICT to NO ACTION
        $ doctrine orm:schema-tool:update --dump-sql
        // no output!!

        So there is no way to update database schema from RESTRICT to NO ACTION and reverse!

        Show
        Jan Obrátil added a comment - I have attached script with two entities. I have this database version: $ mysql --version mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 Check line 44. There is CASCADE. Updating schema by: $ doctrine orm:schema-tool:update Change CASCADE to NO ACTION $ doctrine orm:schema-tool:update --dump-sql ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1; ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE NO ACTION everything ok, so: $ doctrine orm:schema-tool:update Change NO ACTION to RESTRICT $ doctrine orm:schema-tool:update --dump-sql // no output!! Change RESTRICT to CASCADE $ doctrine orm:schema-tool:update --dump-sql ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1; ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE CASCADE everything ok, so: $ doctrine orm:schema-tool:update Change CASCADE to RESTRICT $ doctrine orm:schema-tool:update --dump-sql ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1; ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE RESTRICT everything ok, do: $ doctrine orm:schema-tool:update Change RESTRICT to NO ACTION $ doctrine orm:schema-tool:update --dump-sql // no output!! So there is no way to update database schema from RESTRICT to NO ACTION and reverse!
        Hide
        Jan Obrátil added a comment -

        This is example script with 2 testing entities.

        Show
        Jan Obrátil added a comment - This is example script with 2 testing entities.
        Hide
        Benjamin Eberlei added a comment -

        which database version do yo uuse? can you paste an example schema?

        Show
        Benjamin Eberlei added a comment - which database version do yo uuse? can you paste an example schema?

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Jan Obrátil
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: