Why i can't delete rows from this view that has aggregate function in select clause (ORACLE)?

Question

I've already read about rules for DML operations with views in ORACLE. Most of them seems clear to me, but i'm confused about aggregate function.

For example, I have two tables.

               EMPLOYEES
╔═══════════╦═════════════╦══════════╗
║   emp_id  ║   salary    ║  dept_id ║
╠═══════════╬═════════════╬══════════╣
║   2134    ║   2200      ║    10    ║
║   2327    ║   3100      ║    10    ║
║   2428    ║   4100      ║    20    ║
║   2637    ║   1700      ║    30    ║
╚═══════════╩═════════════╩══════════╝
      \ | /
       \|/
        |
        |    DEPARTMENTS
╔═══════════╦═════════════╦══════════╗
║ dept_id   ║   dname     ║ location ║
╠═══════════╬═════════════╬══════════╣
║    10     ║             ║          ║
║    20     ║             ║          ║
║    30     ║             ║          ║
╚═══════════╩═════════════╩══════════╝

I want a view that shows average employee salary by department. So, i run this SQL:

CREATE OR REPLACE VIEW dept_sals AS
    SELECT d.dept_id, round(avg(e.salary)) AS avg_salary
    FROM employees e
      JOIN
         departments d
      ON (e.dept_id = d.dept_id)
    GROUP BY d.dept_id;

Now i have a view that looks like:

╔═══════════╦════════════╗
║ dept_id   ║ avg_salary ║
╠═══════════╬════════════╣
║    10     ║    2650    ║
║    20     ║    4100    ║
║    30     ║    1700    ║
╚═══════════╩════════════╝

I understand why i can't run update statements against this view. Column "avg_salary" is not a data, it is a dynamically generated information. But why i can't delete a row from this view?

If i try to run:

delete from dept_sals where dept_id = 10;

I get:

ORA-01732: data manipulation operation not legal on this view

What i'm thinking:

Base table DEPARTMENTS has a one-to-one relationship with rows in this view. I guess Oracle can get {dept_id} from view and generate SQL that deletes corresponding row from DEPARTMENTS table. This would not damage tables consistency as information in "avg_salary" is not a data, it's a calculations that we can throw away as department with {dept_id} is not exists anymore.

Answer 1

What you are specifing is a non-updatable view in Oracle. Rules that separate updatable and non-updatable views is specified here from Oracle documenation.

"A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:

A set operator

A DISTINCT operator

An aggregate or analytic function

A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

A collection expression in a SELECT list

A subquery in a SELECT list

A subquery designated WITH READ ONLY

Joins, with some exceptions, as documented in Oracle Database Administrator's Guide"

link --> https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

What you want can be and is solved by using Oracle INSTEAD OF triggers.