Home » RDBMS Server » Server Administration » Inserting into multiple tables through a view
| Inserting into multiple tables through a view [message #374706] |
Mon, 25 June 2001 15:48  |
Ray
Messages: 58
Registered: January 2000
|
Member |
|
|
|
I know that when you join tables in a view, that inserts are allowed because of the join. I have a single source that requires an insert into multiple tables. We are using some mapping middleware that will allow only one input location (Table, view, etc). Is there a way around the joined table view not allowing inserts?
|
|
|
|
|
|
| Re: Inserting into multiple tables through a view [message #374729 is a reply to message #374706] |
Wed, 27 June 2001 05:24  |
Vijay
Messages: 116
Registered: September 1999
|
Senior Member |
|
|
Problem Description:
====================
Summary information on using updatable views based on joined tables. This
entry is particularly useful when encountering ORA-1732 or ORA-1779 errors.
ORA-1732: data manipulation operation not legal on this view
ORA-1779: cannot modify a column which maps to a non-key-preserved view
This information is derived from the Oracle7 Server Administrator's Guide (see
Chapter 12).
NB: THIS FEATURE IS ONLY AVAILABLE AS OF ORACLE 7.3 or higher.
Keywords:
=========
UPDATE
UPDATEABLE
MODIFY
MODIFIABLE
VIEW
1732
1779
KEY
PRESERVED
Solution: DETAILS ON UPDATING VIEWS BASED ON JOINED TABLES
Solution Description:
=====================
Modifying a Join View
A modifiable join view is a view that contains more than one table in the top
level FROM clause of the SELECT statement, and that does not contain any of
the following:
- DISTINCT operator
- aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
- set operations: UNION, UNION ALL, INTERSECT, MINUS
- GROUP BY or HAVING clauses
- START WITH or CONNECT BY clauses
- ROWNUM pseudocolumn
With some restrictions, you can modify views that involve joins. If a view is
a join on other nested views, then the other nested views must be mergeable
into the top level view.
The examples in following sections use the EMP and DEPT tables. These examples
work only if you explicitly define the primary and foreign keys in these
tables, or define unique indexes. Following are the appropriately constrained
table definitions for EMP and DEPT:
CREATE TABLE dept
(
deptno NUMBER(4) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13)
);
CREATE TABLE emp
(
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
job varchar2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);.
You could also omit the primary and foreign key constraints listed above, and
create a UNIQUE INDEX on DEPT (DEPTNO) to make the following examples work.
CREATE OR REPLACE VIEW emp_dept AS
SELECT empno, ename, sal, e.deptno, dname, loc
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno;
Key-Preserved Tables
--------------------
The concept of a key-preserved table is fundamental to understanding the
restrictions on modifying join views. A table is key preserved if every key of
the table can also be a key of the result of the join. So, a key-preserved
table has its keys preserved through a join.
Note: It is not necessary that the key or keys of a table be selected for it
to be key preserved. It is sufficient that if the key or keys were selected,
then they would also be key(s) of the result of the join.
Attention: The key-preserving property of a table does not depend on the
actual data in the table. It is, rather, a property of its schema and not of
the data in the table. For example, if in the EMP table there was at most one
employee in each department, then DEPT.DEPTNO would be unique in the result of
a join of EMP and DEPT, but DEPT would still not be a key-preserved table.
If you SELECT all rows from EMP_DEPT view, the results are:
SELECT * FROM EMP_DEPT;
EMPNO ENAME SAL DEPTNO DNAME LOC
----- ------ ---- ------ ---------- --------
7369 SMITH 800 20 RESEARCH DALLAS
7499 ALLEN 1600 30 SALES CHICAGO
7521 WARD 1250 30 SALES CHICAGO
7566 JONES 2975 20 RESEARCH DALLAS
7654 MARTIN 1250 30 SALES CHICAGO
7698 BLAKE 2850 30 SALES CHICAGO
7782 CLARK 2695 10 ACCOUNTING NEW YORK
7788 SCOTT 3000 20 RESEARCH DALLAS
7839 KING 5500 10 ACCOUNTING NEW YORK
7844 TURNER 1500 30 SALES CHICAGO
7876 ADAMS 1100 20 RESEARCH DALLAS
7900 JAMES 950 30 SALES CHICAGO
7902 FORD 3000 20 RESEARCH DALLAS
7934 MILLER 1430 10 ACCOUNTING NEW YORK
14 rows selected.
In this view, EMP is a key-preserved table, because EMPNO is a key of the EMP
table, and also a key of the result of the join. DEPT is not a key-preserved
table, because although DEPTNO is a key of the DEPT table, it is not a key of
the join.
DML Statements and Join Views
=============================
!!!!!! IMPORTANT !!!!!! IMPORTANT !!!!!! IMPORTANT !!!!!! IMPORTANT !!!!!!
Any UPDATE, INSERT, or DELETE statement performed on a join view can modify
only *** one *** underlying base table.
!!!!!! IMPORTANT !!!!!! IMPORTANT !!!!!! IMPORTANT !!!!!! IMPORTANT !!!!!!
UPDATE Statements:
------------------
The following example shows an UPDATE statement that successfully modifies the
EMP_DEPT view:
UPDATE emp_dept
SET sal = sal * 1.10
WHERE deptno = 10;
The following UPDATE statement would be disallowed on the EMP_DEPT view:
UPDATE emp_dept
SET loc = 'BOSTON'
WHERE ename = 'SMITH';
This statement fails with an ORA-01779 error (cannot modify a column which
maps to a non key-preserved table), because it attempts to modify the
underlying DEPT table, and the DEPT table is not key preserved in the EMP_DEPT
view.
In general, all modifiable columns of a join view must map to columns of a
key-preserved table. If the view is defined using the WITH CHECK OPTION
clause, then all join columns and all columns of repeated tables are not
modifiable.
So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION,
the following UPDATE statement would fail:
UPDATE emp_dept
SET deptno = 10
WHERE ename = 'SMITH';
The statement fails because it is trying to update a join column.
DELETE Statements:
------------------
You can delete from a join view provided there is one and only one
key-preserved table in the join.
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 a DELETE 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 cannot be performed on the view
because both E1 and E2 are key-preserved tables:
CREATE VIEW emp_emp AS
SELECT e1.ename, e2.empno, deptno
FROM emp e1, emp e2
WHERE e1.empno = e2.empno;
If a view is defined using the WITH CHECK OPTION clause and the keypreserved
table is repeated, then rows cannot be deleted from such a view:
CREATE VIEW emp_mgr AS
SELECT e1.ename, e2.ename mname
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno
WITH CHECK OPTION;
No deletion can be performed on this view because the view involves a
self-join of the table that is key preserved.
INSERT Statements:
------------------
The following INSERT statement on the EMP_DEPT view succeeds:
INSERT INTO emp_dept (ename, empno, deptno)
VALUES ('KURODA', 9010, 40);
This statement works because only one key-preserved base table is being
modified (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying
the FOREIGN KEY integrity constraint on the EMP table).
An INSERT statement like the following would fail for the same reason that
such an UPDATE on the base EMP table would fail: the FOREIGN KEY integrity
constraint on the EMP table is violated.
INSERT INTO emp_dept (ename, empno, deptno)
VALUES ('KURODA', 9010, 77);
The following INSERT statement would fail with an ORA-1776 error (cannot
modify more than one base table through a view).
INSERT INTO emp_dept (empno, ename, loc)
VALUES (9010, 'KURODA', 'BOSTON');
An INSERT cannot, implicitly or explicitly, refer to columns of a
non-key-preserved table. If the join view is defined using the WITH CHECK
OPTION clause, then you cannot perform an INSERT to it.
Using the UPDATABLE_ COLUMNS Views
----------------------------------
The following views can assist you when modifying join views:
View Name Description
--------- -----------
USER_UPDATABLE_COLUMNS Shows all columns in all tables and views in the
users schema that are modifiable.
DBA_UPDATABLE_COLUMNS Shows all columns in all tables and views in the
DBA schema that are modifiable.
ALL_UPDATABLE_VIEWS Shows all columns in all tables and views that are
modifiable.
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 18:42:27 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
