CODEWITHSUNDEEP
oracle-database
Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

Oracle enabled disabled constraint in cascade

I'm using

ALTER TABLE WDM_ACCES_TYPE DISABLE CONSTRAINT PK_ACCES_TYPE CASCADE;

to disable all FKs attached to the PK because i'm making some kind of repetitive (weekly) archival script that will copy the database reference tables (the small ones) so that my integrity and indexes stay when copying the archivable data.

The problem is that CASCADE actually disables everything but i have no clue how to enable in CASCADE... Whats the point of having a cascade option in disable and not in enable.

Unless i'm missing something, i either need to know how to enable in cascade or list the dependencies of a PK or FK so i can build my script and nothing i found on the net works.

Thanks

Upvotes: 3

Views: 17708

Answers (1)

Multisync
Multisync

Reputation: 8787

You can use Oracle dictionary to find all dependant foreign key constraints:

SELECT * FROM user_constraints 
WHERE constraint_type = 'R' AND r_constraint_name = 'PK_ACCES_TYPE';

And it's not allowed to use ENABLE + CASCADE when altering a state of a constraint

Upvotes: 4

Related Questions