I'm about to rename a table during a table redesign step in SA 22.214.171.12401. Nothing extraordinary at all.
As there is another table referencing this one with a FK with ON UPDATE CASCADE action, the ALTER TABLE is rejected with SQLCODE -677:
"The table could not be renamed as it has a foreign key with a referential action. To rename the table, first drop the foreign key constraints."
That's understandable, and easy to solve.
However, the V12 docs seem to imply a different behaviour, namely that the rename should work, but FKs will have to be adapted afterwards:
Are the docs wrong in this respect, or is the current behaviour not the intended one?
(IMHO, I'd prefer the failing rename. It seems less error-prone than a successful rename operation that leads to inappropriate/non-working system triggers.)
The wording of the documentation for this behaviour could be improved; first, the foreign key declarations must be dropped, then the table renamed, then the foreign keys re-established to recreate the referential action triggers.
I will work to get this clarified.
answered 17 Jan '11, 12:29
We have improved the behaviour of ALTER TABLE ... RENAME in the 12.0.1 release and in an EBF of 12.0.0 (build 2624) so that ALTER TABLE RENAME will work even if referential triggers with CASCADE actions exist.
The offending text in the documentation, namely
has been removed.
answered 19 Jan '11, 16:36