[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cascade delete in Oracle

Posted on 2007-09-30
8
Medium Priority
?
11,508 Views
Last Modified: 2013-12-19
Hi, just checking whether there's such thing as cascade delete in Oracle? If yes, how do we configure or add the cascade delete? Can it be done through the Oracle enterprise manager, or we must execute query to run it? Please advise, thanks
0
Comment
Question by:rospcc
8 Comments
 
LVL 22

Accepted Solution

by:
JimBrandley earned 1000 total points
ID: 19987471
Yes there is. You enable it when creating foreign key constraints on tables, as:

Alter table your_table ADD CONSTRAINT fk1 FOREIGN KEY( col1) REFERENCES( othertable.othercol) ON DELETE CASCADE;

Jim
 
0
 
LVL 9

Expert Comment

by:konektor
ID: 19987837
check which constraint has which rule in user_constraints, column delete_rule
0
 

Author Comment

by:rospcc
ID: 19988760
Thanks. How do we check whether which key has the cascade delete using Oracle Enterprise Management?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 22

Expert Comment

by:JimBrandley
ID: 19988784
I do not have 9i anymore, and dislike the "Web" OEM with 10g so much I do not use it. Oracle has a free tool you can download that's darned near as good as DBA Studio used to be. It's called SQL Developer. You can also easily run and analyze queries. It's a very nice package.

To check constraints in SQL Developer, open the database you want to examine. Crack the Tables node and select the one you want to examine from the tree. Then from the screen on the right, there's a Constraints tab. Select that. Look at the column in that grid called Delete Rule. For the rows that contain FK constraints, that column will contain CASCADE for those where the cascade is enabled.

Jim
0
 
LVL 8

Expert Comment

by:LindaC
ID: 19992186
I download the Sql Developer and I want to know how can she detect the tnsnames.ora because it only shows 6 connections only.
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19992954
Right-click the Connections node at the top of the connections panel on the left, and select New Database Connection. I did not have any difficulties.

Jim
0
 
LVL 8

Expert Comment

by:LindaC
ID: 19993034
It is a very good utility.  I'am connecting one by one.  I thought that when I hit the right click at the "new connection" and choose tns it would "upload" all my "connection strings" I had on the tnsnames.ora, but I only got about 6 connections.
But it is working ok, conencting one by one.

0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19993135
Good. I like this tool a lot!

Jim
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month17 days, 17 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question