Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6672

Allow Derby to rename tables referenced by foreign keys

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.10.2.0
    • Fix Version/s: 10.11.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Seen in production

      Description

      Hi, I'm on the Apache Roller team and we use database migration scripts to update databases between Roller releases. (We have a common template (http://svn.apache.org/viewvc/roller/trunk/app/src/main/resources/sql/500-to-510-migration.vm?view=co) that is run through Velocity to create specific scripts for the several databases that we support.) One handicap with Derby that we're not seeing with other databases is its inability to rename tables that have FK's on them. Renaming one of our tables returns this error from Derby:

      rename table website to weblog;

      Error: Operation 'RENAME' cannot be performed on object 'SQL140718163851800' because CONSTRAINT 'WP_WEBSITEID_FK' is dependent on that object.
      SQLState: X0Y25
      ErrorCode: 30000
      Error: Operation 'RENAME' cannot be performed on object 'SQL140718163851800' because CONSTRAINT 'WE_WEBSITEID_FK' is dependent on that object.
      SQLState: X0Y25
      ErrorCode: 99999
      Error: Operation 'RENAME' cannot be performed on object 'SQL140718163851800' because CONSTRAINT 'WC_WEBSITEID_FK' is dependent on that object.
      SQLState: X0Y25
      ErrorCode: 99999
      Error: Operation 'RENAME' cannot be performed on object 'SQL140718163851800' because CONSTRAINT 'FO_WEBSITEID_FK' is dependent on that object.
      SQLState: X0Y25
      ErrorCode: 99999
      Error: Operation 'RENAME' cannot be performed on object 'SQL140718163851800' because CONSTRAINT 'MF_WEBSITEID_FK' is dependent on that object.
      SQLState: X0Y25
      ErrorCode: 99999
      Error: Operation 'RENAME' cannot be performed on object 'SQL140718163851800' because CONSTRAINT 'NF_WEBSITEID_FK' is dependent on that object.
      SQLState: X0Y25
      ErrorCode: 99999
      Error: Operation 'RENAME' cannot be performed on object 'SQL140718163851800' because CONSTRAINT 'AP_WEBSITEID_FK' is dependent on that object.
      SQLState: X0Y25
      ErrorCode: 99999

      This results in the migration scripts needing to be messy, first dropping all constraints before recreating them, for the one RDBMS that requires it. It would be great if a future release of Derby could be coded to support table renames regardless of the constraints defined on it. Thanks!

        Attachments

        1. derby-6672-01-aa-allowRenameWithConstraints.diff
          1 kB
          Richard N. Hillegas
        2. derby-6672-01-ab-addTests.diff
          10 kB
          Richard N. Hillegas
        3. docpatch2.patch
          1 kB
          Glen Mazza

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                gmazza Glen Mazza
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: