[
https://issues.apache.org/jira/browse/DERBY-6672?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6672:
---------------------------------
Attachment: derby-6672-01-aa-allowRenameWithConstraints.diff
Attaching derby-6672-01-aa-allowRenameWithConstraints.diff. This patch makes it
possible to rename a referenced table.
With this patch, you can rename the primary and foreign tables and you can
rename the columns in the primary and foreign key constraints. After renaming
the tables and columns, both constraints are still enforced. I have verified
that the DDL produced by dblook looks correct too.
The patch does not address the limitation on CHECK constraints. There seem to
be two cases:
1) Renaming a column mentioned by a CHECK constraint fails because Derby
doesn't know how to re-write the CHECK constraint. I think we could treat that
as a separate, lower-value issue.
2) Renaming the table fails if the table has a CHECK constraint. It ought to be
possible to lift this limitation too.
I have only hand-tested this patch. I haven't tried running the full battery of
tests. And I haven't added any new test cases to the regression tests.
Touches the following file:
M
java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
> Allow Derby to rename tables referenced by foreign keys and also rename
> tables with check constraints
> -----------------------------------------------------------------------------------------------------
>
> 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
>
>
> 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)
