Torque
  1. Torque
  2. TORQUE-45

Generated SQL is in wrong order when dropping tables on table re-creation

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 3.1.1, 3.2
    • Fix Version/s: 4.0-beta1
    • Component/s: Generator
    • Labels:
      None
    • Environment:
      java 1.4.2, 1.5. Solaris 10.

      Description

      When the table creation SQL is re-run when the schema contains foreign keys, you will get referential integrity complaints.
      This is because the table drop order means that it tries to drop dependent tables first.
      To fix this, the drop statements should be before all the creates. They should also be in reverse order to the creates.
      A method to be able to obtain the tables in reverse order will be needed to allow the templates to be modified to fix this.

        Activity

        Hide
        Thomas Fox added a comment -

        There is no "correct order" for all possible constellations. Consider e.g. a circular reference: Table A references B , and B references A.

        Show
        Thomas Fox added a comment - There is no "correct order" for all possible constellations. Consider e.g. a circular reference: Table A references B , and B references A.
        Hide
        Joe Carter added a comment -

        So how are tables with circular references dropped?

        Whatever the root cause, the underlying fault remains, in that you cannot use the generated SQL
        to drop and re-create tables when foreign keys are present.

        Is this a reasonable requirement?

        Show
        Joe Carter added a comment - So how are tables with circular references dropped? Whatever the root cause, the underlying fault remains, in that you cannot use the generated SQL to drop and re-create tables when foreign keys are present. Is this a reasonable requirement?
        Hide
        Thoralf Rickert added a comment -

        > So how are tables with circular references dropped?

        I have this circular references problem in one of my schemes. And my solution is to drop the whole database.

        This isn't a problem in a development environment (it's faster) and later in a production environment you don't drop tables.

        Show
        Thoralf Rickert added a comment - > So how are tables with circular references dropped? I have this circular references problem in one of my schemes. And my solution is to drop the whole database. This isn't a problem in a development environment (it's faster) and later in a production environment you don't drop tables.
        Hide
        added a comment -

        > So how are tables with circular references dropped?
        In some databases, there is the "cascade constraints" option to thge "drop table" command which drops the constraints associated with the dropped table.
        In other databases, I assume one would have to drop the constraints first and then the tables.

        Show
        added a comment - > So how are tables with circular references dropped? In some databases, there is the "cascade constraints" option to thge "drop table" command which drops the constraints associated with the dropped table. In other databases, I assume one would have to drop the constraints first and then the tables.
        Hide
        Thomas Fox added a comment -

        Dropped foreign key before dropping tables

        Show
        Thomas Fox added a comment - Dropped foreign key before dropping tables
        Hide
        Thomas Fox added a comment -

        The solution does not work for foreign keys to external schemata because the external schema table is defined in another sql file than the foreign key. But in my eyes this is acceptable.

        Show
        Thomas Fox added a comment - The solution does not work for foreign keys to external schemata because the external schema table is defined in another sql file than the foreign key. But in my eyes this is acceptable.

          People

          • Assignee:
            Unassigned
            Reporter:
            Joe Carter
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development