Renaming a table

Hi all,

I am migrating from MySQL to Firebird and despite the endless more features it has, I am having trouble making some of the most basic operations, even renaming a table. The super simple syntax:

ALTER TABLE oldname RENAME newname

just does not work. I have even checked IB Expert and it seems that there is nowhere to be found a way to rename a table. Google does not help either.

There is no way to rename a table besides creating a new table with the same fields, copying the data and then dropping the old table? This is a joke, right? Please tell me it is.

I don't think you can easily rename a table, and this should not be done unless you are playing around with your database.
Specific document
Table modifications you can do:
(ref)

Don't get me wrong, but renaming a table is a very common operation during development. If Firebird does not allow it, it does not make it an operation that you make "playing around with your database". I don't want to get into an argument here, but if the system you are accustomed does not allow something, it does not mean that what you are accustomed to is "the right way", it is just "the way you are accustomed to".

You can make a typo in your CREATE TABLE statement, or you can just decide for any reason that the name you originally gave to your table does is not suitable any more, for example because you added or dropped some columns.

Since you are a moderator of the MSSQL and Oracle forums, it would be interesting to know if these two DBMS's do allow table renaming. To me, this is such an obvious and common operation that I would have never even considered the possibility of having a RDBMS that does not allow it.

Wow, what a long response!
All I can say is that I'd always planned my table names (of course using a modeling tool helps) and never had an urgent need to rename them due to adding columns (a customer is always a customer, with ot without email or fax number ) of course I made typos, but got them checking created structures and fixed by drop/create.
Besides design time, renaming a table in a production system is generally impossible without breaking the applications running on top of it, this is why I think you should not rename table, the same for development time, if an even small portion of code has to be rewritten out of your change you'll be pointed as "the one who made us finish late" ...
All this makes me say that this is "the right way" of doing things and not "the way I'm accustomed to".


Both allow renaming, if you are interested in Oracle see this article

You can follow these steps to change the name (not an easy way but it works):
1) create the table with the name that you want (new name)
2) import all the data from the old table (can use DataPump from IB_SQL for example)
3) drop the old table
and voila a new table name

P.S. A good programmer doesn't as far as I see ...

Hi

There is no alter table statement to rename tables, but you always can play with system tables (if you have access to it (sysdba) like this:

UPDATE RDB$RELATIONS
SET RDB$RELATION_NAME='NEWNAME' where
RDB$RELATION_NAME='OLDNAME';


UPDATE RDB$RELATION_FIELDS
SET RDB$RELATION_NAME='NEWNAME' where
RDB$RELATION_NAME='OLDNAME' and
RDB$SYSTEM_FLAG=0;

run both , than commit to database

I hope it helped
Bye