Обсуждение: restore single table

Kevin Duffy wrote:
> I need guidance on how move some changes that I have made to
> my production database.
>
> On my development database I made changes to a table called
> DEPT.  I added a column, added
>
> a couple of records and did some general data cleanup
>
>
>
> What I did not do was change any of the keys on existing
> records. The primary key of DEPT is a
>
> foreign key in several other tables.
>
>
>
> Here is my question:  Can I do a table restore on to the
> production database and expect these
>
> changes to be moved over?  Will the restore handle, via some
> magic, "suspend" the foreign key
>
> constraints and allow the new table structure to be created
> and then populated with new data.
>
>
>
> I have not changed the keys of existing records so that
> existing relationships should be restored.

A few thoughts:

Maybe you do not need to delete and recreate the table.
An ALTER TABLE statement can, for example, add a column to
an existing table.
That way you could leave the foreign key constraints in place
while you do the update.

If you cannot avoid dropping and recreating the table, you
could proceed like this: drop all foreign key constraints
to your table, recreate it and add the constraints again.

You should write an SQL script that does the necessary changes
and test it beforehand.

Lock out all database users while you perform substantial changes
to the database.

Yours,
Laurenz Albe

On Feb 24, 2009, at 1:07 PM, Albe Laurenz wrote:

> Kevin Duffy wrote:
>> I need guidance on how move some changes that I have made to
>> my production database.
>>
> A few thoughts:
>
> Maybe you do not need to delete and recreate the table.
> An ALTER TABLE statement can, for example, add a column to
> an existing table.
> That way you could leave the foreign key constraints in place
> while you do the update.
>
> If you cannot avoid dropping and recreating the table, you
> could proceed like this: drop all foreign key constraints
> to your table, recreate it and add the constraints again.
>
> You should write an SQL script that does the necessary changes
> and test it beforehand.
>
> Lock out all database users while you perform substantial changes
> to the database.


You should also perform these operations in a transaction block so
that you can test (in that session) whether your changes behave as
expected before you commit (or rollback if they don't). Create
savepoints before performing such tests so that typos in your test
queries don't invalidate your schema changes.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,49a52b8b129741404319634!