8 Replies Latest reply on Feb 2, 2016 1:35 AM by 2776946

    Key preserved table deletion: needing help.

    2776946

      Note when I follow the oradoc example (bolded large font below):

      DELETE Statements

      You can delete from a join view provided there is one and only one key-preserved table in the join. The key-preserved table can be repeated in the FROM clause.

      The following DELETE statement works on the emp_dept view:

      DELETE FROM emp_dept WHERE ename = 'SMITH';

      This DELETE statement on the emp_dept view is legal because it can be translated to aDELETE operation on the base emp table, and because the emp table is the only key-preserved table in the join.

      In the following view, a DELETE operation is permitted, because although there are two key-preserved tables, they are the same table. That is, the key-preserved table is repeated. In this case, the delete statement operates on the first table in the FROM list (e1, in this example):

           CREATE VIEW emp_emp

      AS SELECT e1.ename, e2.empno, e2.deptno

           FROM emp e1, emp e2

           WHERE e1.empno = e2.empno;

      I tried the above and it worked great. here is my version of how I tried it

      CREATE VIEW emp_emp

      AS SELECT e1.last_name, e2.employee_id, e2.department_id

      FROM employees2 e1, employees e2

      WHERE e1.employee_id = e2.employee_id; /* (employees table in HR schema)*/

       

       

      delete from emp_emp where employee_id between 100 and 190;

       

      then I tried my own and not so much luck. Note in the below example I am using the employees table from HR schema

      In any case here it is:

       

       

      CREATE VIEW Emp_test AS

          SELECT e1.last_name last_name , e1.employee_id, e2.department_id

             FROM Employees2 e1, Employees2 e2

                  WHERE e1.employee_id = e2.employee_id;

                  delete from emp_test where employee_id = 100;

       

      Note there is one key preserved table here, my underlying employees2 table.



      Error: ORA-01752: cannot delete from view without exactly one key-preserved table

      01752. 00000 -  "cannot delete from view without exactly one key-preserved table"

      *Cause:    The deleted table had

                 - no key-preserved tables,

                 - more than one key-preserved table, or

                 - the key-preserved table was an unmerged view.

      *Action:   Redefine the view or delete it from the underlying base tables.

       

       

      In this case it is just repeated and as the doc says:

      DELETE Statements

      You can delete from a join view provided there is one and only one key-preserved table in the join. The key-preserved table can be repeated in the FROM clause.

      http://docs.oracle.com/database/121/ADMIN/views.htm#ADMIN11782

       

      What am I missing in my script...? I tried a few variations all no luck, I can't figure out what's wrong.

       

      In the doc if you ctrl+f the word 'repeat' it will make finding repeated key preserved topics much easier (that's the only time the word repeat is used)

        • 1. Re: Key preserved table deletion: needing help.
          2776946

          The top variation works fine. The bottom one doesn't. They are basically the same thing. I cannot see why the second one doesn not work..

           

          @image.png

          • 2. Re: Key preserved table deletion: needing help.
            2776946

            wait I think I see it. The first one has employees2,  employees

            the second has   employees2, employees2.....

             

            Sorry didn't mean to waste anyone's time. The doc had me a bit confused...

            • 3. Re: Key preserved table deletion: needing help.
              2776946

              and this is why i got confused:

               

               

              image.png

               

              In the following view, a DELETE operation is permitted, because although there are two key-preserved tables, they are the same table. That is, the key-preserved table is repeated. In this case, the delete statement operates on the first table in the FROM list (e1, in this example):

              CREATE VIEW emp_emp

              AS SELECT e1.ename, e2.empno, e2.deptno

              FROM emp e1, emp e2

              WHERE e1.empno = e2.empno;

              look at my variation:

               

              CREATE VIEW Emp_test AS

                  SELECT e1.last_name last_name , e1.employee_id, e2.department_id

                    FROM Employees2 e1, Employees2 e2

                          WHERE e1.employee_id = e2.employee_id;

                          delete from emp_test where employee_id = 100;

               

              repeated tables.

               

              I noticed an outside source that wrote an article merging both create view and this doc into an article.

               

              Guess what they said:

               

              image.png

               

              which contradicts the oradoc.

               

              Here's the article which is just an abridged version of the docs:

               

              How to Create and Manage Views in Oracle

               

              I think this part of the ORAdoc may be wrong on the example it gave. I the case of the example: image.png

               


              This example doesn't seem to work when repeating the table.....

              • 4. Re: Re: Key preserved table deletion: needing help.
                Jonathan Lewis

                I've used the scott.emp table, in 11.2.0.4 (the output from 12.1.0.2 is the same) - and this is a cut-n-paste from a session that creates the example you seem to be saying doesn't work:

                 

                SQL> create view  emp_emp as

                  2  select e1.ename, e2.empno, e2.deptno

                  3  from emp e1 , emp e2

                  4  where e1.empno = e2.empno

                  5  ;

                 

                View created.

                 

                SQL> delete from emp_emp where empno = 7900;

                 

                1 row deleted.

                 

                SQL> delete from emp_emp where empno between 7400 and 7600;

                 

                3 rows deleted.

                 

                 

                Regards

                Jonathan Lewis

                • 5. Re: Re: Key preserved table deletion: needing help.
                  Jonathan Lewis

                  The manuals are wrong, by the way - you can delete from a join view even with multiple DIFFERENT key-preserved tables in the view definition.

                  Build script - followed by output.

                   

                   

                  create table emp2 as select * from emp;

                  alter table emp2 add constraint e2_pk primary key(empno);

                  select count(*) from emp2;

                   

                  create or replace  view  emp_emp as

                  select e1.ename, e2.empno, e2.deptno

                  -- from emp e1 , emp2 e2

                  from emp2 e1 , emp e2

                  where e1.empno = e2.empno

                  ;

                   

                  delete from emp_emp where empno = 7900;

                  delete from emp_emp where empno between 7400 and 7600;

                   

                  select count(*) from emp;

                  select count(*) from emp2;

                   

                  -----

                   

                  Table created.

                   

                   

                  Table altered.

                   

                   

                    COUNT(*)

                  ----------

                          14

                   

                  1 row selected.

                   

                   

                  View created.

                   

                   

                  1 row deleted.

                   

                   

                  3 rows deleted.

                   

                   

                    COUNT(*)

                  ----------

                          14

                   

                  1 row selected.

                   

                   

                    COUNT(*)

                  ----------

                          10

                   

                  1 row selected.

                   

                   

                  The script has two options for the FROM clause, and (as the manual actually says) the FIRST table in the from clause is the one from which the rows are deleted.

                  I thought I'd written something about this several years ago, but if I have I can't find the article.

                   

                  Regards

                  Jonathan Lewis

                  1 person found this helpful
                  • 6. Re: Key preserved table deletion: needing help.
                    2776946

                    I was reading more into it today and I saw something from a textbook similar to what you posted and it worked. I think I may have some constraint on my underlying employees2 table keeping me from doing my delete.

                     

                    Thanks much for the help.

                    • 7. Re: Key preserved table deletion: needing help.
                      2776946

                      Also the doc got me a bit confused in the repetition rules. I think it has more than 1 thing wrong in that respect which really threw me off.

                       

                      For example as you mentioned, more than one key preserved table can be included, however the first one gets deleted. In more than one doc it mentions 'only one key preserved can be included'

                       

                      Think I'm going to have to try setting up the emp schema and trying the script for myself.

                      • 8. Re: Key preserved table deletion: needing help.
                        2776946

                        this has been completely bugging me and I finally figured out I had a session parameter bug which was preventing me from doing this :

                        CREATE VIEW Emp_test AS

                            SELECT e1.last_name last_name , e1.employee_id, e2.department_id

                               FROM Employees2 e1, Employees2 e2

                                    WHERE e1.employee_id = e2.employee_id;

                                    delete from emp_test where employee_id = 100

                         

                         

                        which was my original problem....

                         

                        Had the session running up for a few days must've been something I altered earlier...

                         

                        In any case disregard my post.

                         

                         

                         

                        Please disregard this post