You can't truncate a table if it has a foreign key constraint enabled.
"damorgan" <damorgan_at_exesolutions.com> wrote in message
news:3CAA1367.E3971F0C_at_exesolutions.com...
> Sorry but I just have to ask? Did someone assign you to do something
simple
> in the most difficult convoluted way they could think of?
>
> Why not just go in and do the following:
>
> CREATE TABLE temp AS
> SELECT *
> FROM mytable;
>
> TRUNCATE TABLE mytable;
>
> INSERT INTO mytable
> SELECT *
> FROM temp
> ORDER BY primary_key_fields_or_whatever;
>
> DROP TABLE temp;
>
> Of course five minutes later it won't be organized anymore. If this is a
> requirement consider using an Index Organized Table (IOT).
>
> Daniel Morgan
>
>
>
> Mike F wrote:
>
> > I am writing a perl script which will re-organie a table by the primary
> > key. I have two options
> >
> > First,
> > a)create the temp table as select * from origianl table
> > b)drop all the indexes on the original table
> > c)disable all the child table foreign constraints related to this table
> > d)truncate the original table
> > f)re-insert all the rows from that temp table
> > g)re-create all the indexes
> > f)re-enable all the child table constraints
> >
> > This approach works fine, the problems is, for some big tables with 1
> > million row, the data is not available for about 30 minutes.
> >
> > Second approach
> > a)create the temp table as select * from origianl table
> > b)create all the indexes on the temp table
> > c)find those child table, and add foreign constraints pointing to the
> > temp table
> > d)create all the comments on the temp table and columns
> > f)rename original table to temp and rename the temp table to target
> > table
> >
> > this approach, data is always available to users,
> >
> > my question is, for my second approach, did I list everything I have to
> > worry about?
> >
> > What will happen to the triggers on the table? Will it point to the
> > new table or will it point to the original table which is now renamed
> > to temp? Is there anything else I need to worry?
> >
> > Thanks
> >
> >
> >
> > --
> > Sent by dbadba62 from hotmail in area com
> > This is a spam protected message. Please answer with reference header.
> > Posted via http://www.usenet-replayer.com/cgi/content/new
>
Received on Thu Apr 04 2002 - 12:00:34 CST
