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!

Delete row, forein key

Discussion in 'SQL PL/SQL' started by monkey, Apr 26, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello, All!
    Please, If we want to delete a row in a first table that contains id which is foreign key in second table,
    why do we have to delete the whole column in that other table containing it as foreigh key?

    Is there ay other option (meaning, delete just thse elements from that column (in the second table), that were mentioned in that row we delete from first table

    Please, can U help me with keywords an links about that topic? I am new in that area, and still do not manage all the terms in english, which is not my native tounge.

    MANY THANKS!!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    WHY would you preserver only parts of a record??? Doing so makes no sense whatsoever. Apparently your understanding of referential integrity isn't any better than your understanding of triggers.


    A PRIMARY KEY ensures uniqueness of the data in a given table. A FOREIGN KEY references the PRIMARY KEY of the parent table to ensure no records are inserted that contain unreferenced primary key values. Deleting a parent record makes the child records useless as the key those child records reference no longer exists. Oracle will NOT let you delete a parent key if a child record exists, therefore you MUST delete ALL child records BEFORE deleting the parent record:


    Code (SQL):

    SQL> --
    SQL> -- Add primary key to emp
    SQL> --
    SQL> ALTER TABLE emp
      2  ADD CONSTRAINT emp_pk
      3  PRIMARY KEY(empno)
      4  USING INDEX tablespace indx;


    TABLE altered.


    SQL>
    SQL> --
    SQL> -- Add primary key to dept
    SQL> --
    SQL> ALTER TABLE dept
      2  ADD CONSTRAINT dept_pk
      3  PRIMARY KEY(deptno)
      4  USING INDEX tablespace indx;


    TABLE altered.


    SQL>
    SQL> --
    SQL> -- Add foreign key to emp
    SQL> -- referencing dept
    SQL> --
    SQL> ALTER TABLE emp
      2  ADD CONSTRAINT emp_dept_fk
      3  FOREIGN KEY (deptno)
      4  REFERENCES dept;


    TABLE altered.


    SQL>
    SQL> --
    SQL> -- Try to delete from dept
    SQL> --
    SQL> DELETE FROM dept;
    DELETE FROM dept
    *
    ERROR at line 1:
    ORA-02292: integrity CONSTRAINT (GRIBNAUT.EMP_DEPT_FK) violated - child record
    found

    SQL>
    SQL> --
    SQL> -- Looks like we have an error
    SQL> --
    SQL> -- Delete dependent records first
    SQL> --
    SQL> DELETE FROM emp;


    18 ROWS deleted.


    SQL>
    SQL> --
    SQL> -- Now delete from dept
    SQL> --
    SQL> DELETE FROM dept;


    4 ROWS deleted.


    SQL>
    SQL> ROLLBACK;


    ROLLBACK complete.


    SQL>
    SQL> --
    SQL> -- Modify the foreign key
    SQL> --
    SQL> -- Delete child records on delete
    SQL> -- of parent
    SQL> --
    SQL> ALTER TABLE emp DROP CONSTRAINT emp_dept_fk;


    TABLE altered.


    SQL>
    SQL> ALTER TABLE emp
      2  ADD CONSTRAINT emp_dept_fk
      3  FOREIGN KEY (deptno)
      4  REFERENCES dept ON DELETE cascade;


    TABLE altered.


    SQL>
    SQL> --
    SQL> -- Now delete from dept
    SQL> --
    SQL> DELETE FROM dept;


    4 ROWS deleted.


    SQL>
    SQL> ROLLBACK;


    ROLLBACK complete.


    SQL>
     

    Using the ON DELETE CASCADE option when building the foreign key constraint will delete child records when a parent key is deleted. It isn't widely used as in most cases this is NOT what you would want to do.