cancel
Showing results for 
Search instead for 
Did you mean: 

Renaming a table with dependent foreign keys with trigger actions fails

VolkerBarth
Contributor
4,052

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.)

Accepted Solutions (2)

Accepted Solutions (2)

Former Member

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.

VolkerBarth
Contributor
0 Kudos

...meaning those triggers will be adapted automatically? (That's a fast improvement, for sure!)

Former Member

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

Has something changed here from your last comment?

Breck_Carter
Participant
0 Kudos

It is curious, since ALTER TABLE test1 RENAME test11 works in both V11 and V12.

reimer_pods
Participant
0 Kudos

@Breck: I wonder how you tested that; with DBISQL and DBISQLC like Arthoor I too get the error
Could not execute statement.
Table 'test2' has a foreign key with a referential action
SQLCODE=-667, ODBC 3 State="HY000"
no matter if the new table name is test22 or test11.
Using 12.0.1.3726

Breck_Carter
Participant
0 Kudos

@Reimer: Get a bandage ready... then look carefully... vewy vewy carefully... at the ALTER TABLE test1 RENAME test11 statement... now apply bandage to forehead 🙂

reimer_pods
Participant
0 Kudos

Sigh ... looks like I need new reading glasses 😉

VolkerBarth
Contributor
0 Kudos

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:)

Former Member

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.

VolkerBarth
Contributor
0 Kudos

Thanks for the clarification - and for the improved wording in the question's title:)

Answers (0)