 | |
Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Temporary tables in memory using PL/SQL (as a workaround for nothaving CREATE TABLE privs)? -
02-15-2008
, 01:37 PM
|
Why not just insert the records and delete the old ones afterwards? |
are part of composite primary keys. In your first example, you update
a foreign key value.
For some reason, I thought it wasn't possible to update values that
were part of a primary key. I didn't think it was allowed. Using
JavaBeans, I've been told, it isn't allowed. Is it bad practice to
update values that are part of a primary key? Was it ever not allowed
in any version of Oracle? Not sure where I got that idea from, but
your first example works fine.
Anyone care to comment on the goodness or badness of updating values
in a composite primary key rather than making copies of rows, deleting
the old rows, then re-inserting updated versions of the copied rows?
I did come up with a way of doing what I needed using "collections of
records" in PL/SQL before I saw your reply. That seems to work. I have
not tried your example #2 yet. I can post it if anyone is interested.
#22
| |||
| |||
|
|
On Feb 14, 2:44 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: Why not just insert the records and delete the old ones afterwards? Thanks Urs. Some of the columns in the child tables I need to update are part of composite primary keys. In your first example, you update a foreign key value. For some reason, I thought it wasn't possible to update values that were part of a primary key. I didn't think it was allowed. Using JavaBeans, I've been told, it isn't allowed. Is it bad practice to update values that are part of a primary key? Was it ever not allowed in any version of Oracle? Not sure where I got that idea from, but your first example works fine. You can, and IIRC always could, change primary key (doesn't matter if |
constraints. You can
- update a primary key, if it has no children
- update a foreign key, if it's new parents are there. That's what I
did in my firts example.
There is yet another way to update primary keys and foreign keys in
one transaction: deferrable constraints, which means that integrity will
be checked only at commit time. Search the manuals...
Hth Urs Metzger
#23
| |||
| |||
|
|
On Feb 14, 2:44 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: Why not just insert the records and delete the old ones afterwards? Thanks Urs. Some of the columns in the child tables I need to update are part of composite primary keys. In your first example, you update a foreign key value. For some reason, I thought it wasn't possible to update values that were part of a primary key. I didn't think it was allowed. Using JavaBeans, I've been told, it isn't allowed. Is it bad practice to update values that are part of a primary key? Was it ever not allowed in any version of Oracle? Not sure where I got that idea from, but your first example works fine. You can, and IIRC always could, change primary key (doesn't matter if |
constraints. You can
- update a primary key, if it has no children
- update a foreign key, if it's new parents are there. That's what I
did in my firts example.
There is yet another way to update primary keys and foreign keys in
one transaction: deferrable constraints, which means that integrity will
be checked only at commit time. Search the manuals...
Hth Urs Metzger
#24
| |||
| |||
|
|
On Feb 14, 2:44 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: Why not just insert the records and delete the old ones afterwards? Thanks Urs. Some of the columns in the child tables I need to update are part of composite primary keys. In your first example, you update a foreign key value. For some reason, I thought it wasn't possible to update values that were part of a primary key. I didn't think it was allowed. Using JavaBeans, I've been told, it isn't allowed. Is it bad practice to update values that are part of a primary key? Was it ever not allowed in any version of Oracle? Not sure where I got that idea from, but your first example works fine. You can, and IIRC always could, change primary key (doesn't matter if |
constraints. You can
- update a primary key, if it has no children
- update a foreign key, if it's new parents are there. That's what I
did in my firts example.
There is yet another way to update primary keys and foreign keys in
one transaction: deferrable constraints, which means that integrity will
be checked only at commit time. Search the manuals...
Hth Urs Metzger
#25
| |||
| |||
|
|
On Feb 14, 2:44 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: Why not just insert the records and delete the old ones afterwards? Thanks Urs. Some of the columns in the child tables I need to update are part of composite primary keys. In your first example, you update a foreign key value. For some reason, I thought it wasn't possible to update values that were part of a primary key. I didn't think it was allowed. Using JavaBeans, I've been told, it isn't allowed. Is it bad practice to update values that are part of a primary key? Was it ever not allowed in any version of Oracle? Not sure where I got that idea from, but your first example works fine. You can, and IIRC always could, change primary key (doesn't matter if |
constraints. You can
- update a primary key, if it has no children
- update a foreign key, if it's new parents are there. That's what I
did in my firts example.
There is yet another way to update primary keys and foreign keys in
one transaction: deferrable constraints, which means that integrity will
be checked only at commit time. Search the manuals...
Hth Urs Metzger
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.