Glen Mazza created DERBY-6672:
---------------------------------
Summary: Allow Derby to rename tables even if constraints defined
on it
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
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)
