But, if one were to drop the triggers and the foreign keys and then
perform the CREATE and DROP statements as specified and then recreate
the foreign keys and triggers (with the new table name, naturally),
then this should work properly, should it not?

-ken

On 23-Aug-05, at 4:34 AM, Lester Caine wrote:

> David Johnson wrote:
>
>
>> According to SQL92 specifications ...
>>
>> create newtable as
>> select * from oldtable;
>>
>> drop oldtable;
>>
>> But this doesn't seem to work in Firebird. A review of the Interbase
>> documentation suggests that there is no direct method. You will
>> need to
>> create your new table, migrate your data, and then drop the old
>> table.
>>
>
> The main reason it is not allowed is that unlike the simple SQL92
> spec,
> we have triggers and stored procedures that may be using the oldtable,
> and we have foreign keys that may be linking to it, all of which
> need to
> be managed when changing the name.
>
> Personally I like the security of building a clean new database and
> pumping data from the old one. It may take a long time, but it ensures
> that there are no problems with any of the data :)
>
> --
> Lester Caine
> -----------------------------
> L.S.Caine Electronic Services
>
>
> ------------------------ Yahoo! Groups Sponsor --------------------
> ~-->
> <font face=arial size=-1><a href="http://us.ard.yahoo.com/
> SIG=12hi32mc6/M=362131.6882499.7825260.1510227/D=groups/
> S=1705115386:TM/Y=YAHOO/EXP=1124793272/A=2889191/R=0/SIG=10r90krvo/
> *http://www.thebeehive.org
> ">Get Bzzzy! (real tools to help you find a job) Welcome to the
> Sweet Life - brought to you by One Economy</a>.</font>
> --------------------------------------------------------------------
> ~->
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>