Home » SQL & PL/SQL » SQL & PL/SQL » Key preserved
Key preserved [message #242967] Tue, 05 June 2007 11:17 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

created a view VI as

Create Or Replace View VI
As
Select D.DEPTNO "Dept Number",EMPNO||','||ENAME "Empl"
From EMP E,DEPT D
Where E.DEPTNO = D.DEPTNO
And D.DEPTNO = 10
Order By EMPNO;

Then i gave a command

SQL> Delete From VI ;
Delete From VI
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table

Can anyone plase explain me what is key-preserved table?

Empno is primary key of EMP table and
Deptno is primary key of DEPT table
Re: Key preserved [message #242976 is a reply to message #242967] Tue, 05 June 2007 11:29 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
ORA-01752: cannot delete from view without exactly one key-preserved table
Cause: The deleted table either had no key perserved tables, had more than one key-preserved table, or the key-preserved table was an unmerged view or a table from a read-only view.
Action: Redefine the view or delete it from the underlying base tables.
Re: Key preserved [message #242979 is a reply to message #242976] Tue, 05 June 2007 11:32 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

But sir i don't know the meaning of key preserved table
Re: Key preserved [message #242980 is a reply to message #242967] Tue, 05 June 2007 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
Another perspective is that DELETE only works against a single table. With the join in the VIEW you are asking row(s) in TWO tables to be deleted; which is NOT allowed.
Re: Key preserved [message #242987 is a reply to message #242979] Tue, 05 June 2007 12:07 Go to previous message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
A key preserved table is a table in which the key is preserved through the join. For eg. in the standard emp and dept tables empno is the primary key of the emp table while deptno is the primary key of the dept table. Now, were you to create a join view like:
create view v1
as select empno, ename, dept.deptno, dname
from emp, dept
where emp.deptno=dept.deptno
then, while the primary key of the emp table would have been preserved through the join(it becomes a key preserved table), the primary key of the dept table would not have survived the join.

[Updated on: Wed, 06 June 2007 07:13]

Report message to a moderator

Previous Topic: CHECK THE STATUS IN FOR LOOP CURSOR
Next Topic: group by... ORA-22818 subquery expression not allowed here"
Goto Forum:
  


Current Time: Tue Dec 06 00:02:54 CST 2016

Total time taken to generate the page: 0.19488 seconds