Disable constraint cascade

Hi -

If you use the "cascade" option when disabling a constraint ("alter table table_name disable constraint constraint_name cascade"), when you re-enable the constraint, it doesn't seem to enable any constraints for any other tables that it may have affected. Is this true? Is there a way around this?

In the following example (see ddl below), I have three tables, TEST_A_TBL,TEST_B_TBL, and TEST_C_TBL. (A, B, and C for short.) Table B has a foreign key that references table A, and table C references table B and I've specified on delete cascade for each reference. If I have records in each, and delete a record in A, child records in B and C properly delete.

Now, suppose I disable the constraints for B, specifying "cascade". Then I reenable the constraints. Now, if I delete a record in A, the child record in B is deleted, but not in C. In my actual case, the dependencies are complicated and there are a lot of tables like C; I'd prefer not having to find all of them and trying to figure out the right order to disable & reeable them...

create table TEST_A_TBL(FLD_A_ONE NUMBER PRIMARY KEY,

                        FLD_A_TWO VARCHAR2(16)); create table TEST_B_TBL(FLD_B_ONE NUMBER,

                        FLD_B_TWO VARCHAR2(16),
CONSTRAINT XPK_TBL_B PRIMARY KEY (FLD_B_ONE), CONSTRAINT XREF_TBL_B FOREIGN KEY (FLD_B_ONE) REFERENCES TEST_A_TBL(FLD_A_ONE) ON DELETE CASCADE
);

create table TEST_C_TBL(FLD_C_ONE NUMBER,

                        FLD_C_TWO VARCHAR2(16),
CONSTRAINT XPK_TBL_C PRIMARY KEY (FLD_C_ONE), CONSTRAINT XREF_TBL_C FOREIGN KEY (FLD_C_ONE) REFERENCES TEST_B_TBL(FLD_B_ONE) ON DELETE CASCADE
);

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 06 1999 - 17:03:09 CDT