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!