Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Key preserved table deletion: needing help.

2776946Feb 1 2016 — edited Feb 1 2016

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)

This post has been answered by Jonathan Lewis on Feb 1 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 29 2016
Added on Feb 1 2016
8 comments
2,030 views