Cascading Updates

From: Oleg Kuzmin <kuzmin_at_hotmail.com>
Date: 2000/05/16
Message-ID: <si339t5rrj048@corp.supernews.com>#1/1

Greetings!

When I create a table in Oracle 8 using PL/SQL script, the following syntax
(sample fragment) is used:

CREATE TABLE TEST_TABLE
(

    VALUE1          VARCHAR2(10)  NOT NULL,
    VALUE2          VARCHAR2(50)  NOT NULL,
    VALUE3          VARCHAR2(4) NOT NULL,

    PRIMARY KEY (VALUE1,VALUE3),
    FOREIGN KEY (VALUE2,VALUE3) REFERENCES TEST_TABLE1(VALUE2,VALUE3) ON DELETE CASCADE
);

If I want to delete the record in the parent table (TEST_TABLE1), it'll propagate the deletions of related records in the child table (TEST_TABLE). However, if I try to change a value in the parent table, and there are child records with the foreign key value being changed, it gives me an error:

ORA-02292: integrity constraint (TEST_DATABASE.SYS_C002255) violated - child record found

Is there an easy way in Oracle to propagate updates in the parent table to all its children? The only way I see it possible is through triggers, and I'd like to avoid it.

I'll really appreciate any help on this issue.

Thanks,

Oleg Received on Tue May 16 2000 - 00:00:00 CDT