|
I'm about to rename a table during a table redesign step in SA 12.0.0.2601. 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:
Question: 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. |
|
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. ...meaning those triggers will be adapted automatically? (That's a fast improvement, for sure!) 1
Yes, that's right. An ALTER TABLE RENAME will now succeed and any referential triggers will be re-created automatically. Replies hidden
1
It does NOT work for me in SA 12.0.1.3726. Sample SQL statements: CREATE TABLE test1 (id INTEGER PRIMARY KEY); CREATE TABLE test2 (id2 INTEGER PRIMARY KEY, id INTEGER, FOREIGN KEY id(id) REFERENCES test1(id) ON UPDATE CASCADE ON DELETE CASCADE); ALTER TABLE test2 RENAME test22; // SQLCODE = -667
(21 May '12, 08:29)
Arthoor
It is curious, since ALTER TABLE test1 RENAME test11 works in both V11 and V12.
(21 May '12, 09:24)
Breck Carter
@Breck: I wonder how you tested that; with DBISQL and DBISQLC like Arthoor I too get the error
(21 May '12, 10:52)
Reimer Pods
@Reimer: Get a bandage ready... then look carefully... vewy vewy carefully... at the ALTER TABLE test1 RENAME test11 statement... now apply bandage to forehead :)
(21 May '12, 12:23)
Breck Carter
Sigh ... looks like I need new reading glasses ;-)
(22 May '12, 03:35)
Reimer Pods
Well, I can't tell, however the issue I had brought up just dealt with renaming the parent table (i.e. test1) - and that does work as expected. I haven't dealt with renaming the child table... Besides that, I would think that the improvement Glenn had announced and the wording in the docs don't tell between both kinds of renames, so both should work:)
(22 May '12, 05:05)
Volker Barth
|