I am building a database using Oracle SQL Developer. I want to add a foreign key integrity constraint that cascades on update. However, when I run the following query I get an error. There is also a red line below the word UPDATE and when I hover over it, it says "Syntax Error: Partially recognized rules"

ALTER TABLE JOURNAL
ADD CONSTRAINT fk_journal_Doc
FOREIGN KEY (Doc_id)
REFERENCES DOCUMENTS(Doc_id)
ON UPDATE CASCADE;
/

Error:

Error starting at line : 92 in command -
ALTER TABLE JOURNAL
ADD CONSTRAINT fk_journal_Doc
FOREIGN KEY (Doc_id)
REFERENCES DOCUMENTS(Doc_id)
ON UPDATE CASCADE
Error report -
ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

I am using Oracle version 21.4.3.063

Any help with solving this issue would be very much appreciated!

>Solution :

Oracle supports only ON DELETE CASCADE.

As simple as that.

---

If you want to implement it, use a database trigger. Here’s an example.

Sample tables first (in master-detail relationship; source are Scott’s EMP and DEPT tables):

SQL> create table t_dept as select deptno, dname from dept;

Table created.

SQL> alter table t_dept add constraint pk_td primary key (deptno);

Table altered.

SQL> create table t_emp as select deptno, empno, ename from emp;

Table created.

SQL> alter table t_emp add constraint pk_te primary key (empno);

Table altered.

SQL> alter table t_emp add constraint fk_te_td foreign key (deptno)
  2    references t_dept (deptno);

Table altered.

Trigger:

SQL> create or replace trigger trg_au_tdept
  2    after update of deptno on t_dept
  3    for each row
  4  begin
  5    update t_emp e set
  6      e.deptno = :new.deptno
  7      where e.deptno = :old.deptno;
  8  end;
  9  /

Trigger created.

Original tables’ contents:

SQL> select * from t_dept order by deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> select * from t_emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7782 CLARK
        10       7839 KING
        10       7934 MILLER
        20       7566 JONES
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN

14 rows selected.

Let’s update department 10 to 11:

SQL> update t_dept set deptno = 11 where deptno = 10;

1 row updated.

Result:

SQL> select * from t_dept order by deptno;

    DEPTNO DNAME
---------- --------------
        11 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> select * from t_emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        11       7782 CLARK    --> This is the effect of
        11       7839 KING     --> trigger's
        11       7934 MILLER   --> "on update cascade"
        20       7566 JONES
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN

14 rows selected.

SQL>