Database - Introduction to SQL - Discussion

Cascading means if we take action on some thing then the references will get affected automatically

Example:

Like If we update record of master table the child table record will get update .


Like if take example of trigger:
create or replace trigger cascade_update
after update of c1 on t1
for each row
begin
update t2
set c2 = :new.c1
where c2 = :old.c1;
end;
/

SQL> update t1
2 set c1 = 2
3 where c1 = 1;

1 row updated.

SQL> select * from t2;

C2
----------
2
table t2 record updated automatically.


This feature is data integrity of sql...............

The accuracy and consistency of stored data, indicated by an absence of any alteration in data between two updates of a data record. Data integrity is imposed within a database at its design stage through the use of standard rules and procedures, and is maintained through the use of error checking and validation routines.

And by the means of cascade it is ensured that all the related table which contain the updated data will be updated when an row is updated. And the effect would be reflected.

In oracle parent-child relation table is there on that time you delete the parent record or row then it shows error.

It means the parent table is associated with a child that why the parent record not delete. If u delete the parent records before we drop the child table record, it is not possible to the real time that why we are using cascade keyword mention in child table creation otherwise you drop the parent time use.

EX: Drop table parent table name cascade.

ON UPDATE CASCADE indicates that if we update the parent, then the change is cascaded (affected) to the child easily.
However, Data integrity means maintaining and assuring the accuracy and consistency of data over its entire life-cycle. Therefore, Cascade directly leads to data integrity.

For Example: If you delete a company, you might also want to delete the company's history of addresses.

In my issue I had to define some additional operations (trigger) for updating concert's table. Those operations had to modify club_name and band_name. I was unable to do it, because of reference. I couldn't modify concert and then deal with club and band tables. I couldn't also do it the other way. ON UPDATE CASCADE was the key to solve the problem.

Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table.

Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.

Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

Cascade means whenever you will perform any modification in a data that has its key as a foreign key in another table.

The same modification will be performed in the another tables on rows which have that foreign key.

If we apply cascade property then we can perform delete, update operation even if primary key and foreign keys are applied to main and child table.

Sir how did say that answer is correct? Sir you explain shortly.

How actually the data integrity is ensured in the cascade?