Analyzing Dangling References(How to detect?)

Lesson 3	Analyzing dangling references
Objective	Detect dangling references.

Analyzing Oracle Dangling References

Oracle allows you to delete an object that is the target of the REF without deleting the reference to it.
For example, if an object is referenced within an object table, Oracle allows you to delete a record from the object, which is referenced within the object table.
Oracle qualifies this state with a name: a dangling REF. This is roughly equivalent to what would happen if you deleted a department record without changing the records of the employees within the department.
For example, the SALE_HEADER object references the CUSTOMER_TYPE object. If a sale has been made to Anne Nicole and the record within CUSTOMER_TYPE for Anne Nicole is deleted, the sale record associated with that name is referred to as dangling.
There is no declarative way to prevent dangling REFs, but Oracle allows you to identify such records by using the

IS DANGLING

clause within SQL.
The following MouseOver explains the variables in the syntax:

SELECT column_name_1, <column_name_2>
FROM table_name alias
WHERE object_name is DANGLING;

The SELECT clause for selecting attributes from the object table
The FROM clause for specifying the name of the object table
The IS DANGLING clause

Dangling Reference Syntax
The following diagram uses an example to explain the variables in the syntax:

SELECT product_id, log_datetime
FROM  pet_care_log_obj_table
WHERE product_id is DANGLING;

The SELECT clause
The FROM clause
The IS DANGLING clause

Dangling Reference Example

The IS DANGLING clause helps identify a dangling reference and update it with a suitable reference. Let us look at the example of Anne Nicole. After the CUSTOMER_TYPE record for Anne Nicole has been deleted, the associated sale record is now dangling. To resolve this, you can create a dummy CUSTOMER_TYPE record and update all such records with a REF to this dummy record.

How can we avoid dangling references?

To avoid dangling references, you can write pre-delete triggers on the table that contains the parent object. For example, a predelete trigger can be defined on a child record that has a reference to its parent record. This trigger will check whether the parent record exists. If the parent record exists, the trigger will prevent the user from deleting the child record.
You can also use a foreign key in combination with REF to avoid this issue. To do so, you will have to include an additional attribute within the child object and include a FOREIGN KEY clause within the CREATE TABLE statement for the parent table.
Pre-delete trigger: A pre-delete trigger is a trigger which is fired before a record is deleted from a table.
In the next lesson, we will write a query using the DEREF data type to retrieve related data.

Now that you have learned about detecting dangling references, click the link below to learn more about dangling references.
Detecting Dangling References