This discussion is archived
7 Replies Latest reply: Aug 16, 2013 9:59 AM by L-MachineGun RSS

Update column data to Upper Case in parent and child table

AAG Newbie
Currently Being Moderated

Hi ,

 

I am facing issue while updating column value to upper case in parent table and child table. How can i do that ?

 

when updating parent row:

ORA-02292: integrity constraint (XXXXXXXXXXXXXX_FK) violated - child record found

 

When updatng corresponding child row:

ORA-02291: integrity constraint (XXXXXXXXXXXXXXXX_FK) violated - parent key not found

 

how can i update on both the places ?

 

Regards,

AA

  • 1. Re: Update column data to Upper Case in parent and child table
    L-MachineGun Pro
    Currently Being Moderated
    ALTER TABLE child_tab DISABLE CONSTRAINT XXXXXXXXXXXXXX_FK;
    UPDATE child_tab SET parent_key = UPPER(parent_key);
    UPDATE parent_tab SET the_key = UPPER(the_key);
    COMMIT;
    ALTER TABLE child_tab ENABLE CONSTRAINT XXXXXXXXXXXXXX_FK;

  • 2. Re: Update column data to Upper Case in parent and child table
    AAG Newbie
    Currently Being Moderated

    I did like to this in PROD and its difficult for taking that FK constraint off and update.

    We have that option as a backup. But is there any other way i can think of ?

  • 3. Re: Update column data to Upper Case in parent and child table
    L-MachineGun Pro
    Currently Being Moderated

    AAG wrote:

     

    I did like to this in PROD and its difficult for taking that FK constraint off and update.

    We have that option as a backup. But is there any other way i can think of ?

    Yes, perhaps using the DBMS_REDEFINITION package which in summary involves creating a copy of the tables, doing the update and then renaming the new tables.

  • 4. Re: Update column data to Upper Case in parent and child table
    tmbeetz Explorer
    Currently Being Moderated

    Hi,

     

    if the FK Colum allows NULL change set cild=NULL, change primary key to upper and then set child to upper.

     

    Regards

    Thomas

  • 5. Re: Update column data to Upper Case in parent and child table
    tmbeetz Explorer
    Currently Being Moderated

    Hi,

     

    have a look for "deferrable constraints"

     

    Regards,

    Thomas

  • 6. Re: Update column data to Upper Case in parent and child table
    rp0428 Guru
    Currently Being Moderated

     

    I am facing issue while updating column value to upper case in parent table and child table. How can i do that ?

     

    Why do you need to do that?

     

    That is just ONE of several questions you should answer before you start modifying your data.

     

    1. What is your 4 digit Oracle version? (result of SELECT * FROM V$VERSION)

     

    2. If both values are the same case what difference does it make what that case is?hen you don't need to alter your original data.

     

    3. What is the source of the column values you are using now? If you change your data to upper case it will no longer be identical to the source data.

     

    4. What is your plan for enforcing future values to be stored in UPPER case? Are you going to use a trigger? Have you written and tested such a trigger to see if it will even work the way you expect?

     

    5. Why aren't you using a surrogate key instead of a 'business' data item? You have just demonstrated one reason why surrogate keys can be useful: their actual value is NOT important.

     

    You should reexamine your problem and architecture and consider other alternatives.

     

    One alternative is to add a new 'surrogate key' column to use as the primary key. Just create a new sequence and use a trigger to populate the new column. Your current plans will require a trigger to perform the case conversion so instead of the just use the trigger to provide the value.

     

    If the change is being done to facilitate searching you could just add a VIRTUAL column UPPER_MY_COLUMN and index that instead. Then you could search on that new virtual column and the data values would still be identical to the original data source.

  • 7. Re: Update column data to Upper Case in parent and child table
    L-MachineGun Pro
    Currently Being Moderated

    You could try case insensitive query:

     

    ALTER SESSION SET NLS_COMP=ANSI;
    ALTER SESSION SET NLS_SORT=BINARY_CI;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points