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

asked 17 Jan '11, 10:40

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 17 Jan '11, 12:30

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104


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.

permanent link

answered 17 Jan '11, 12:29

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

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

(17 Jan '11, 12:39) Volker Barth

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.

permanent link

answered 19 Jan '11, 16:36

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

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

(19 Jan '11, 17:02) Volker Barth
1

Yes, that's right. An ALTER TABLE RENAME will now succeed and any referential triggers will be re-created automatically.

(19 Jan '11, 17:44) Glenn Paulley
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


Has something changed here from your last comment?

(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
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

(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
showing 2 of 8 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×415
×61
×25
×11

question asked: 17 Jan '11, 10:40

question was seen: 2,217 times

last updated: 22 May '12, 05:05