1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to do Force Update?

Discussion in 'SQL PL/SQL' started by Panneerselvam, Sep 7, 2013.

  1. Panneerselvam

    Panneerselvam Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I have more than one or more than four tables. one table is having referential integrity with other table. i have to update one field in parent table. it will change to all my child table. Pls help me?.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi selvam,

    We need to post this question in SQL PL/SQL Section for quick Responses. Please follow the section wise questions for quick responses and appropriate answers. For now I have moved this post to appropriate section.
     
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hello,

    - As far as I know, there is no cascade update, that is, updating a row in the parent table does not automatically update the referencing rows in the child tables.

    - In a well designed application (which therefore conforms to relational database modeling), primary keys should be immutable (you should never update them)

    - You should have created indexes on your foreign keys, otherwise the whole child table will be locked upon updates on the parent table.

    So, before thinking about how to do this update, you may want to check your application design.

    Anyway for an example, have a look at Example-9-15 of the online documentation:
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you updating the primary key of your parent table?
     
  5. Panneerselvam

    Panneerselvam Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thank you Dude....
     
  6. Panneerselvam

    Panneerselvam Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Bcos , I updated wrongly , that's y i need help ....
     
  7. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    That was precisely the question we asked, why do you update a primary key which is supposed to be immutable?

    The important thing is not to know whether you updated correctly or wrongly the primary key, the real question is why do you update a primary key? That's against relational data modeling.

    And I said that from a PL/SQL implementation point of view it was possible (albeit strongly prohibited from an application design point of view) and therefore I gave you the link to the oracle online documentation that illustrates this with an example.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To do this effectively and NOT use PL/SQL you will need to disable the foreign key constraints on the child tables, update the parent table primary key values, update the child table foreign key values then enable the constraints again. You cannot directly update either table while the constraints are enabled as it will involve inserts on the parent table before updates can occur on the child tables, then the olde 'keys' will need to be deleted. Here is an example of doing it 'right':

    Code (SQL):
    SQL> ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);
     
    TABLE altered.
     
    SQL>
    SQL> ALTER TABLE emp
      2  ADD CONSTRAINT emp_dept_fk FOREIGN KEY(deptno)
      3  REFERENCES dept;
     
    TABLE altered.
     
    SQL>
    SQL> SELECT dname, deptno FROM dept;
     
    DNAME              DEPTNO
    -------------- ----------
    ACCOUNTING             10
    RESEARCH               20
    SALES                  30
    OPERATIONS             40
     
    SQL>
    SQL> SELECT empno, ename, deptno
      2  FROM emp;
     
         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7369 SMITH              20
          7499 ALLEN              30
          7521 WARD               30
          7566 JONES              20
          7654 MARTIN             30
          7698 BLAKE              30
          7782 CLARK              10
          7788 SCOTT              20
          7839 KING               10
          7844 TURNER             30
          7876 ADAMS              20
     
         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7900 JAMES              30
          7902 FORD               20
          7934 MILLER             10
     
    14 ROWS selected.
     
    SQL>
    SQL> ALTER TABLE emp disable CONSTRAINT emp_dept_fk;
     
    TABLE altered.
     
    SQL>
    SQL> UPDATE dept SET deptno=50 WHERE deptno=30;
     
    1 ROW updated.
     
    SQL>
    SQL> UPDATE emp SET deptno=50 WHERE deptno=30;
     
    6 ROWS updated.
     
    SQL>
    SQL> ALTER TABLE emp enable CONSTRAINT emp_dept_fk;
     
    TABLE altered.
     
    SQL>
    SQL> SELECT dname, deptno FROM dept;
     
    DNAME              DEPTNO
    -------------- ----------
    ACCOUNTING             10
    RESEARCH               20
    SALES                  50
    OPERATIONS             40
     
    SQL>
    SQL> SELECT empno, ename, deptno
      2  FROM emp;
     
         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7369 SMITH              20
          7499 ALLEN              50
          7521 WARD               50
          7566 JONES              20
          7654 MARTIN             50
          7698 BLAKE              50
          7782 CLARK              10
          7788 SCOTT              20
          7839 KING               10
          7844 TURNER             50
          7876 ADAMS              20
     
         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7900 JAMES              50
          7902 FORD               20
          7934 MILLER             10
     
    14 ROWS selected.
     
    SQL>
     
     
  9. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    If we choose this method, then I think we have also assume that we are not talking about a multi-user environment. If it is the case, then obviously your suggested pure SQL solution is much more efficient than the sequence of triggers that has to be run upon each update.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot update the primary key in a multi-user envieonment and do it successfully; this is an operation that requires restricted session to complete or a full shutdown of the application. I cannot see how to do this with triggers or PL/SQL; the foreign key MUST be disabled or the records requiring a key update MUST be duplicated in the parent table BEFORE any child updates can occur. EIther operaiton disallows multi-user transactions.

    Prove me wrong with a working example.
     
  11. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Yes, I agree that all foreign key constraints on child tables must be disabled, otherwise upon the update on the parent table, oracle will raise ORA-02292: integrity constraint . . . violated - child record found.

    I don't understand what do you mean by duplicated within this context. How can you duplicate a primary key which is unique in the same table?

    What I meant by PL/SQL was that from the moment that there is no more enabled constraint, you can use triggers to CASCADE the UPDATE (this is what the OP asked for at the end of his comment). Here is an example of the online documentation Example 9-15 UPDATE CASCADE Trigger for Parent Table
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The data other than the key must be duplicated with the new key values -- there will then be 2x records with the same non-key data with two different pk values so the update of the child tables can succeed.

    You never mentioned tha the foreign key needs to be disabled before such triggers will work.
     
  13. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Ok, I think I see what you mean, if we choose not to shut down the application and maintain the foreign keys, we have to proceed according to the following order:

    1) duplicate data in the parent table, that is first INSERT the new primary key with other non key columns associated with old primary key.

    2) COMMIT the new INSERTED data in the parent table

    3) UPDATE the primary key values in the child tables with the new primary key values which are now available in the parent table.

    4) COMMIT the updated values in child tables

    5) DELETE the rows with old primary key values from the parent table.

    6) Thanks to the ON DELETE CASCADE clause defined on the foreign key definition of each child table, the rows referencing the old primary key values (which have just been removed from the parent table) are also going to be removed from child tables.

    7) COMMIT

    Did I understand your point correctly?

    Yes, you're right, sorry for that.


    PS: Is this your blog?
     
  14. Panneerselvam

    Panneerselvam Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thank you for ur valuable Concept. Today i did the update like disable all the constraint and update the field, then enable the constraint.

    Thank you very much....
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're welcome.
     
  16. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, you understand the concept. Of course the ON DELETE CASCADE will have nothing to do with respect to the child tables should the update of the foreign keys succeed.

    Yes, that is my blog; is there something wrong with it?
     
    dariyoosh likes this.
  17. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    yes, thanks for pointing this out and sorry again for my lack of attention. It seems that today I just make mistake one after another. Maybe it's time for me to go to bed. :D

    No, just curious :)

    Thanks again for your nice problem description.
     
  18. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which problem description? I think there are many in the span of my posts.