on 2011 Jan 17 10:40 AM
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:
RENAME new-table-name
Change the name of the table to new-table-name. Any applications using the old table name must be modified, as necessary. After the renaming operation succeeds, foreign keys with ON UPDATE or ON DELETE actions must be dropped and re-created, as the system-created triggers used to implement these actions continue to refer to the old name.
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.)
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
After the renaming operation succeeds, foreign keys with ON UPDATE or ON DELETE actions must be dropped and re-created, as the system-created triggers used to implement these actions continue to refer to the old name.
has been removed.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, that's right. An ALTER TABLE RENAME will now succeed and any referential triggers will be re-created automatically.
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
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:)
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.