db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6672) Allow Derby to rename tables referenced by foreign keys
Date Tue, 29 Jul 2014 14:27:38 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14077755#comment-14077755
] 

Rick Hillegas commented on DERBY-6672:
--------------------------------------

Code to verify that you can't rename a referenced table goes back to the open-sourcing of
Derby. It is in the original version of renameTable.sql (later converted to RenameTableTest.java).
So the motivation for this restriction is not documented in Derby's code archaeology. Maybe
someone from IBM can look up the motivation for this restriction? Thanks.

> Allow Derby to rename tables referenced by foreign keys
> -------------------------------------------------------
>
>                 Key: DERBY-6672
>                 URL: https://issues.apache.org/jira/browse/DERBY-6672
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.10.2.0
>            Reporter: Glen Mazza
>         Attachments: derby-6672-01-aa-allowRenameWithConstraints.diff, derby-6672-01-ab-addTests.diff,
docpatch2.patch
>
>
> 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!



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message