Let's consider a schema depicted in the ERD shown above. We see that there are one-to-many relationship between the authors and books table. Specifically, the author_id column in the books table references the authors table. In this context, we call the authors table as parent and books a child.
In a relational database, a parent table is a table that is referenced by one or more other tables through a foreign key relationship. The table referencing the primary key of the parent table is called child table. This enforces referential integrity in the database.
However, what if you want to delete a row from the parent table? Particularly, if you're trying to delete a row from the parent table that is currently referenced by its child table, Oracle will throw an error:
When you have a parent-child relationship between tables in a database, deleting a row from the parent table without appropriately handling the related rows in the child table can lead to orphaned records. Orphaned records are child records that reference a non-existent parent, and they can cause referential integrity issues and data inconsistencies. To maintain data integrity, Oracle provides options for defining the behavior when a row from the parent table is deleted.
ON DELETE CASCADE
If ON DELETE CASCADE is specified, it means that when a referenced row in the parent table is deleted, all corresponding rows in the child table will be automatically deleted. This ensures that there are no orphaned records in the child table. The basic syntax is as follows:
ALTER TABLE child_tableADD CONSTRAINT fk_constraintFOREIGN KEY (parent_id)REFERENCES parent_table (id)ON DELETE CASCADE;
ON DELETE SET NULL or DEFAULT
If ON DELETE SET NULL is specified, when a referenced row in the parent table is deleted, the foreign key columns in the corresponding rows of the child table are set to NULL. This is suitable when the relationship is optional, and you want to allow child records without a parent reference. The basic syntax of ON DELETE SET NULL is:
ALTER TABLE child_tableADD CONSTRAINT fk_constraintFOREIGN KEY (parent_id)REFERENCES parent_table (id)ON DELETE SET NULL;
Similar to SET NULL, but sets the foreign key columns to their default values. This works when you have specified a default value in the child table. The basic syntax is as follows:
ALTER TABLE child_tableADD CONSTRAINT fk_constraintFOREIGN KEY (parent_id)REFERENCES parent_table (id)ON DELETE SET DEFAULT;
DELETE vs TRUNCATE
In Oracle, the DELETE statement removes rows from a table based on a specified condition. If no condition is specified, it will delete all rows. The DELETE statements basically evaluate conditions, delete rows, and fires any associated triggers at individual row-level. The DELETE statement is a DML and is transactional. Each row deleted using the DELETE statement is logged in the database redo log generation. It can be rolled back to undo changes made by the statement if a transaction is not committed.
On the other hand, the TRUNCATE statement is a DDL (Data Definition Language) and is used to remove all rows from a table, without checking individual rows. Thus, while you cannot have any condition-based deletion, TRUNCATE is usually faster than DELETE statement, especially when dealing with large amounts of data. It also does not fire triggers associated with the table and ignores any referential integrity constraints.
0 Comments