Re: dynamic sql

On Sep 10, 12:07 pm, durga <kanagadurga.sunda..._at_gmail.com> wrote:
> hi folks,
>
> i am an oracle novice....i am working on a small project, i've
> created a few tables holding employee, projects and clients info. My
> parent table is OGEMP whose primary key is SSN, there are 5 other
> tables whose foreign key is SSN(referring OGEMP).
> Here's my problem, if an employee says that his SSN has been entered
> wrong, then I should be able to modify my OGEMP table and insert the
> new SSN and delete the old one. I have to use a bottom-up approach
> here, since there are other tables who have SSN as foreign keys.
> I am supposed to use dynamic SQL for this problem. I got a clue
> here........first I've to create a new table which is going to hold my
> OGEMP and related tables in a particular order (parent to child), then
> insert the new ssn as a new record in the parent table, update in all
> my child tables and finally delete the old SSN from the OGEMP table.
> I've created the new table.........but my problem is inserting rows
> (tables in parent to child order)...........I've to use dynamic SQL
> and user_constriants......help please!!!!

You need to update a PK which has FK on it. So insert the new Parent. Update all child rows that point to the old parent then delete the old parent all within a single transaction. What is the problem?

The other way around this is to assign a generated key to the parent row and hook all the FK to the generated key rathet than the natural key, SSN, in this case. Now you can change the SSN without affect the children but your application has to be sure to validate the SSN against the master. It will pretty much have to do this to get the generated key value which will be part of the child tables inherited key.

Finally, if you are going to store an SSN number it should be encrypted which means you may not want to use it as an inherited key.

HTH -- Mark D Powell -- Received on Mon Sep 10 2007 - 12:20:36 CDT