Forum Stats

  • 3,723,806 Users
  • 2,244,620 Discussions
  • 7,850,715 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Key preserved table deletion: needing help.

2776946
2776946 Member Posts: 351
edited February 2016 in SQL & PL/SQL

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)

Chris Hunt2776946

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,562 Gold Crown
    edited February 2016 Accepted Answer

    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

    Chris Hunt27769462776946

Answers

  • 2776946
    2776946 Member Posts: 351
    edited February 2016

    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

  • 2776946
    2776946 Member Posts: 351
    edited February 2016

    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...

  • 2776946
    2776946 Member Posts: 351
    edited February 2016

    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.....

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,562 Gold Crown
    edited February 2016

    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

    2776946
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,562 Gold Crown
    edited February 2016 Accepted Answer

    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

    Chris Hunt27769462776946
  • 2776946
    2776946 Member Posts: 351
    edited February 2016

    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.

  • 2776946
    2776946 Member Posts: 351
    edited February 2016

    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.

  • 2776946
    2776946 Member Posts: 351
    edited February 2016

    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

This discussion has been closed.