Cannot modify a column which maps to a non key preserved table ошибка

A DML table expression clause is only useful when you need columns from more than one table. In your case, you can use a regular update with an EXISTS:

update web_userrole
set role = replace(role, 'FULL', 'READ')
where read_only <> 'Y'
    and exists
    (
        select 1/0
        from web_userdatasource
        where datasource = p_datasource
            and username = web_userrole.username
    );

If you really do need to use columns from both tables you have three options:

1. repeat the join in the SET and the WHERE clause. This is easy to build but not optimal.
2. DML table expression. This should work, if you have the correct indexes.

3. MERGE, below is an example.

   merge into web_userrole
   using
   (
       select distinct username
       from web_userdatasource
       where datasource = p_datasource
   ) web_userdatasource on
   (
       web_userrole.username = web_userdatasource.username
       and web_userrole.read_only <> 'Y'
   )
   when matched then update
   set role = replace(role, 'FULL', 'READ');
   

This does not directly answer your question, but instead provides some work-arounds. I can’t reproduce the error you’re getting. I’d need a full test case to look into it further.

Generic advice for updatable views

One of the main problems with updatable views is the large number of restrictions on the queries they can contain. The query or view must not contain a lot of features, such as DISTINCT, GROUP BY, certain expressions, etc. Queries with those features may raise the exception «ORA-01732: data manipulation operation not legal on this view».

The updatable view query must unambiguously return each row of the modified table only one time. The query must be “key preserved”, which means Oracle must be able to use a primary key or unique constraint to ensure that each row is only modified once.

To demonstrate why key preserved is important, the below code creates an ambiguous update statement. It creates two tables, the first table has one row and the second table has two rows. The tables join by the column A, and try to update the column B in the first table. In this case it’s good that Oracle prevents the update, otherwise the value would be non-deterministic. Sometimes the value would be set to «1», sometimes it would be set to «2».

--Create table to update, with one row.
create table test1 as
select 1 a, 1 b from dual;

--Create table to join two, with two rows that match the other table's one row.
create table test2 as
select 1 a, 1 b from dual union all
select 1 a, 2 b from dual;

--Simple view that joins the two tables.
create or replace view test_view as
select test1.a, test1.b b_1, test2.b b_2
from test1
join test2 on test1.a = test2.a;

--Note how there's one value of B_1, but two values for B_2.
select *
from test_view;

A  B_1  B_2
-  ---  ---
1    1    1
1    1    2

--If we try to update the view it fails with this error:
--ORA-01779: cannot modify a column which maps to a non key-preserved table
update test_view
set b_1 = b_2;

--Using a subquery also fails with the same error.
update
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
)
set b_1 = b_2;

The MERGE statement does not have the same restrictions. The MERGE statement appears to try to detect ambiguity at run time, instead of compile time.

Unfortunately MERGE doesn’t always do a good job of detecting ambiguity. On Oracle 12.2, the below statement will occasionally work, and then fail. Making small changes to the query may make it work or fail, but I can’t find a specific pattern.

--The equivalent MERGE may work and changes "2" rows, even though there's only one.
--But if you re-run, or uncomment out the "order by 2 desc" it might raise:
--  ORA-30926: unable to get a stable set of rows in the source tables
merge into test1
using
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
    --order by 2 desc
) new_rows
    on (test1.a = new_rows.a)
when matched then update set test1.b = new_rows.b_2;

UPDATE fails at compile time if it is theoretically possible to have duplicates. Some statements that should work won’t run.

MERGE fails if the database detects unstable rows at run time. Some statements that shouldn’t work will still run.

define cusname=’GEORGE’;

INSERT INTO (select s.prd_id, s.cus_id, s.qty_sold, s.price from sales s, customers c where s.cus_id=c.cus_id)

VALUES (102,(select cus_id from customers where upper(c_name) like ‘%GEORGE%’),14,(select price from product where prd_id = 102)*14)

I am getting the following error when I am trying to run the above query.

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table

01779. 00000 —  «cannot modify a column which maps to a non key-preserved table»

*Cause:    An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.

*Action:   Modify the underlying base tables directly.

CUSTOMERS TABLE

PRODUCT TABLE

SALES TABLE

Hello everyone am trying to insert data into base tables via a view that joins more than two tables. I have an instead of trigger that should insert the data into the tables, not sure where have gone wrong on the code. The following is the codes have used.

View

CREATE OR REPLACE FORCE VIEW "NEW_APPLICANT" ("FIRST_NAME", "LAST_NAME", "ADDRESS",
     "MATH_SCORE", "ENGLISH_SCORE", "SCIENCE_SCORE") AS 
select APPLICANT.FIRST_NAME as FIRST_NAME,
       APPLICANT.LAST_NAME as LAST_NAME,
       APPLICANT.ADDRESS as ADDRESS,
       RESULT.MATH_SCORE as MATH_SCORE,
       RESULT.ENGLISH_SCORE as ENGLISH_SCORE,
       RESULT.SCIENCE_SCORE as SCIENCE_SCORE 
from   RESULT RESULT, APPLICANT APPLICANT 
where  APPLICANT.APPLICANT_ID=RESULT.APPLICANT_ID
/

Trigger

create or replace TRIGGER application_new_insert
   INSTEAD OF INSERT ON NEW_APPLICANT
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN
     INSERT INTO APPLICANT
       (FIRST_NAME, LAST_NAME, ADDRESS)
     VALUES (
     :new.FIRST_NAME,
     :new.LAST_NAME,
     :new.ADDRESS);
   INSERT INTO RESULT ( MATH_SCORE, ENGLISH_SCORE, SCIENCE_SCORE)
   VALUES (
     :new.MATH_SCORE,
     :new.ENGLISH_SCORE,
     :new.SCIENCE_SCORE);
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or order ID');
   END application_new_insert;

Table definitions. Table applicant:

CREATE TABLE "APPLICANT" 
( "APPLICANT_ID" NUMBER NOT NULL ENABLE, 
  "FIRST_NAME" VARCHAR2(45) NOT NULL ENABLE, 
  "LAST_NAME" VARCHAR2(45) NOT NULL ENABLE, 
  "ADDRESS" VARCHAR2(35), 
  CONSTRAINT "PK_APPLICANT_ID" 
    PRIMARY KEY ("APPLICANT_ID") ENABLE 
) / 

CREATE OR REPLACE TRIGGER "TR_APPLICANT" 
before insert on APPLICANT 
for each row 
begin 
  if :NEW.APPLICANT_ID is null then 
    select APPLICANT_ID_SEQ.nextval 
    into :NEW.APPLICANT_ID 
    from dual; 
  end if; 
end; / 

ALTER TRIGGER "TR_APPLICANT" 
  ENABLE / 

Table result:

CREATE TABLE "RESULT" 
( "RESULT_ID" NUMBER NOT NULL ENABLE, 
"APPLICANT_ID" NUMBER NOT NULL ENABLE, 
"MATH_SCORE" NUMBER NOT NULL ENABLE, 
"ENGLISH_SCORE" NUMBER NOT NULL ENABLE, 
"SCIENCE_SCORE" NUMBER NOT NULL ENABLE, 
CONSTRAINT "PK_RESULT_ID" PRIMARY KEY ("RESULT_ID") ENABLE 
) / 

ALTER TABLE "RESULT" 
  ADD CONSTRAINT "FK_RESULT_APPLICANT_ID" 
    FOREIGN KEY ("APPLICANT_ID") 
    REFERENCES "APPLICANT" ("APPLICANT_ID") 
  ENABLE / 

CREATE OR REPLACE TRIGGER "TR_RESULT" 
before insert on RESULT 
for each row 
begin 
  if :NEW.RESULT_ID is null then 
    select RESULT_ID_SEQ.nextval 
    into :NEW.RESULT_ID 
    from dual; 
  end if; 
end; / 

ALTER TRIGGER "TR_RESULT"
  ENABLE / 

Insert to Oracle View failed with ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> create view emp_dept as select ename, dname from emp, dept where emp.deptno=dept.deptno;

View created.

SQL> insert into emp_dept (select * from emp_dept);

insert into emp_dept (select * from emp_dept)

*

ERROR at line 1:

ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> drop view emp_dept;

View dropped.

SQL> create view v_emp as select ename from emp ;

View created.

SQL>  insert into v_emp (select * from v_emp);

14 rows created.

What is a Key-Preserved Table?

Before you understand what a key-value save table is, you must first knowUpdatable join view This concept, the key-value save table is just a table that holds the field information that is allowed to be updated. Why is there such a table? Let’s take a step by step.

Understand the concept of «view», «join view», «updatable join view»

viewCan be divided into view objects and inline views.

— View Object
in the databaseView objectLike a table or index, it is a kind of database object. It is actually an object that is provided to an external query after virtualizing a layer based on the original data in the table.
The essence is not to save the actual result of the query, but just save the query in the database, when the user queries a view, it will find and execute the statement of the view.
andJoin viewIn fact, it is just to associate two tables in one view.

-- Create a test table based on the table under the SCOTT user
CREATE TABLE EMP_T AS
     SELECT *
     FROM EMP;

CREATE TABLE DEPT_T AS
     SELECT *
     FROM DEPT;

 -- Create a view object (join view)
CREATE OR REPLACE VIEW EMP_DEPT_V
     AS
          SELECT E.EMPNO
               , E.ENAME
               , E.SAL
               , E.DEPTNO
               , D.DNAME
          FROM EMP_T E, DEPT_T D
          WHERE E.DEPTNO = D.DEPTNO;
 

— Inline View
In SQL statements, nested statements can be written in many places. For example, after FROM, nested parentheses can be nested with other statements, followed by WHERE, SELECT, UPDATE, INSERT, and DELETE. It can be written. Just write the location is different, the way of execution is different from the processing restrictions, the SQL nested in these SQL is calledSubquery . Subqueries can be divided into the following categories:

• Inline view: The location is in the FROM statement and is equivalent to the concept of a preprocessed result set.
• Scalar Subquery: A subquery that returns only one result value. A subquery located in a SELECT statement can only return one value, so it must be a scalar subquery. Such a subquery will also appear in the WHERE statement.
• Associated subquery: The form in which the result of the query is passed to the subquery as a parameter
• General subquery: general usage except for special forms of subqueries

-- Examples of inline views
 -- Join inline view
SELECT *
FROM
     (SELECT E.EMPNO
           , E.ENAME
           , E.SAL
           , E.DEPTNO
           , D.DNAME
      FROM EMP_T E, DEPT_T D
      WHERE E.DEPTNO = D.DEPTNO
     );

So what is the Updatable Join View?

Quite simply, it’s a grammatical form of putting a join view (including view objects and inline views) into a UPADTE statement.

Temporarily do not consider the meaning of the SQL statement, just for testing

-- Based on view objects
UPDATE EMP_DEPT_V
SET ENAME = ENAME || '-' || DEPTNO;

 -- Based on inline view
UPDATE
     (SELECT E.EMPNO
           , E.ENAME
           , E.SAL
           , E.DEPTNO
           , D.DNAME
      FROM EMP_T E, DEPT_T D
      WHERE E.DEPTNO = D.DEPTNO
     )
SET ENAME = ENAME || '-' || DEPTNO;

Follow the step by step, to this step, do not execute this statement, first guess what is the result of the execution of these two statements?

Understand the principle of error reporting

According to the normal connection logic, the relationship between DEPT_T and EMP_T is a one-to-many relationship, that is, one department can correspond to multiple employees, and one employee can only belong to one department at a time.

-- If EMP_T has the following data
EMPNO | ENAME | DEPTNO
7839  , KING   , 10
7935  , MILLER , 10

 -- If DEPT_T has the following data
DEPTNO | DNAME
10     , ACCOUNTING

At this time, if the two tables are joined and the DEMPNO in EMP_T is replaced by DNAME, it is obvious that the value in DEPTNO will be replaced with ACCOUNTING.

EMPNO | ENAME | DEPTNO | DNAME
7839  , KING   , 10    , ACCOUNTING
7935  , MILLER , 10    , ACCOUNTING

However, if there are two DEPTNO data in DEPT_?

-- If DEPT_T has the following data
DEPTNO | DNAME
10     , ACCOUNTING
10     , MARCKEING

The result of the join will become:

EMPNO | ENAME | DEPTNO | DNAME
7839  , KING   , 10    , ACCOUNTING
7839  , KING   , 10    , MARCKEING
7935  , MILLER , 10    , ACCOUNTING
7935  , MILLER , 10    , MARCKEING

Which value should the value in the 10th department be replaced with? Therefore, in this case, the database does not know how to deal with it, only one can be reported.ORA-01779: Unable to modify column corresponding to non-key-value save tablemistake.

So how can I see this key-value save table? ORACLE provides a view.

SELECT *
FROM DBA_UPDATABLE_COLUMNS
WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP_DEPT_V';

OWNER | TABLE_NAME | COLUMN_NAME | UPDATABLE | INSERTABLE | DELETABLE
SCOTT   EMP_DEPT_V     EMPNO            NO          NO          NO
SCOTT   EMP_DEPT_V     ENAME            NO          NO          NO
SCOTT   EMP_DEPT_V     SAL              NO          NO          NO
SCOTT   EMP_DEPT_V     DEPTNO           NO          NO          NO
SCOTT   EMP_DEPT_V     DNAME            NO          NO          NO

From here you can see the viewEMP_DEPT_VThe fields in it cannot be changed.

So how can we make them change? According to the above, if the data in the DEPT_T table can be guaranteed to be unique, it can be updated. That is to say, add on the DEPT_T table.Primary key constraintorUnique constraint。
Because if you don’t impose constraints, the database can’t judge whether it is unique, and the constraint is actually telling the database, «Do not worry, change it!»

ALTER TABLE DEPT_T
     ADD CONSTRAINT PK_DEPT_T PRIMARY KEY (DEPTNO);

Look at the previous table (if the result is different from the following, rebuild the view):

SELECT *
FROM DBA_UPDATABLE_COLUMNS
WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP_DEPT_V';

OWNER | TABLE_NAME | COLUMN_NAME | UPDATABLE | INSERTABLE | DELETABLE
SCOTT   EMP_DEPT_V     EMPNO            YES         YES         YES
SCOTT   EMP_DEPT_V     ENAME            YES         YES         YES
SCOTT   EMP_DEPT_V     SAL              YES         YES         YES
SCOTT   EMP_DEPT_V     DEPTNO           YES         YES         YES
SCOTT   EMP_DEPT_V     DNAME             NO          NO          NO

At this time, you can see that the original fields in the EMP_T table in the view are all updatable.

You can execute the previous statement and test it.

in conclusion:
When making changes to the join view, you must ensure that the modified value is unique, and this fact is known to the database, and the method that tells the database is to establish a primary key constraint or a unique constraint.

Then the question is coming. Is there any way to change without adding constraints? Not all tables are free to build these constraints.

the solution:

• Add to/*+ BYPASS_UJVC */ Prompt, let ORACLE skip the check (not valid after 11g R2, not recommended)
• rewriteUPDATEStatement, judged by other means
• willUPDATEChange intoMERGEStatement

Refer to the ORACLE official documentation for explanation:

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.

An updatable join view (also referred to as a modifiable join view) is a view that contains multiple tables in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.