Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to overwrite a table [only data]?

How to overwrite a table [only data]?

From
"Ezequias Rodrigues da Rocha"
Date:
Hi list,<br /><br /> I would like to overwrite a table with problem with his original. But there are some points.<br
/><br/> I can't just delete and restore it becouse it has a foreign key and when I try to delete it ask for drop
cascade.<br/><br /> As I know the drop cascade delete in both tables the rows related. Is it correct ?<br /><br /><br
/>Regards...<br clear="all" /><br />-- <br />
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<br
/>                                  Atenciosamente(Sincerely)<br />                        Ezequias Rodrigues da Rocha
<br/> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<br />A pior das democracias ainda é
melhordo que a melhor das ditaduras<br />The worst of democracies is still better than the better of dictatorships <br
/><ahref="http://ezequiasrocha.blogspot.com/">http://ezequiasrocha.blogspot.com/</a>

Re: How to overwrite a table [only data]?

From
"Harald Armin Massa"
Date:
Ezequias ,

I would like to overwrite a table with problem with his original. But there are some points.

I can't just delete and restore it becouse it has a foreign key and when I try to delete it ask for drop cascade.

As I know the drop cascade delete in both tables the rows related. Is it correct ?

You are right.

Your database structure disallows this kind of deletes.

So, if you want to allow it, you have to disable / remove that foreign key restriction, refresh your data, and reimplement that restriction.

Be aware that foreign key restrictions usually are there for a reason.

Harald




--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Re: [SQL] How to overwrite a table [only data]?

From
"Aaron Bono"
Date:
On 10/4/06, Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com> wrote:
Hi list,

I would like to overwrite a table with problem with his original. But there are some points.

I can't just delete and restore it becouse it has a foreign key and when I try to delete it ask for drop cascade.

As I know the drop cascade delete in both tables the rows related. Is it correct ?

You can drop the foreign keys and then the table.  Then when you recreate and repopulate the table, you can add the foreign keys back at the very end.


==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

Re: [SQL] How to overwrite a table [only data]?

From
Markus Schaber
Date:
Hi, Ezequias,

Ezequias Rodrigues da Rocha wrote:

> I would like to overwrite a table with problem with his original. But
>  there are some points.
> 
> I can't just delete and restore it becouse it has a foreign key and
> when I try to delete it ask for drop cascade.

As long as the delete and restore is in the same transaction, SET
CONSTRAINTS ALL DEFERRED might help.

See the docs for more info.

> As I know the drop cascade delete in both tables the rows related. Is
> it correct ?

Yes, cascade will delete all referencing rows in the otehr table to keep
the constraints intact, keeping only those rows that have NULL in the
foreign key. I think that's not exactly what you want.

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org