Cascade Introduction and Usage (Oracle)

Cascade Delete, such as when you delete a table and then add the keyword, will delete the table and delete the relationship between the table otherObject

1. Cascade delete the information in table A, when the field in table A refers to the field in table B, once the information in the field in B is deleted, the information of table A is also automatically deleted. (When the parent table information is deleted, the child table information is also automatically deleted)

For example, the basic information of the employee and the company's department information are stored separately in the following two tables. We are
CREATE TABLE Dept
(Deptno number (TEN) is not NULL,
Deptname VARCHAR2 (+) NOT NULL,
Constraint Pk_dept primary KEY (DEPTNO));
And
CREATE TABLE EMP
(empno number (TEN) is not NULL,
fname VARCHAR2 (20),
LName VARCHAR2 (20),
Dept Number (10),
Constraint Pk_emp primary KEY (Empno));

And then we now add the foreign key to try on the delete cascade

ALTER TABLE EMP
Add constraint fk_emp_dept foreign key (dept) References Dept (deptno) on DELETE cascade;
Increase the foreign key first. Then insert the data.
INSERT INTO Dept values (1, ' Sales Department ');
INSERT INTO Dept values (2, ' finance Department ');
INSERT into EMP values (2, ' Mary ', ' Song ', 1);
INSERT into EMP values (3, ' Linda ', ' Liu ', 2);
INSERT into EMP values (4, ' Linlin ', ' Zhang ', 1);
And now I have to delete the sales department, what will be the consequences?
Delete from dept where deptno = 1;
We found that in addition to a dept in the data is deleted, EMP in the two data is also deleted, where two of the EMP data is referenced by the sales department of this data, it is easy to understand on the delete cascade.

Next we look at the delete set NULL, as the name implies, the foreign KEY constraint established in this way, when the referenced data is deleted, the corresponding value of the data referenced by the data will become null, and the following is a test to prove that the on delete set NULL effect:
First restore the data that you just made, and then change the constraint:
ALTER TABLE EMP
Add constraint fk_emp_dept foreign key (dept) References Dept (deptno) on delete set null;
We then perform the delete operation:
Delete from dept where deptno = 1;
You will also find that in addition to the Sales Department in dept, the value of the Dept of the two data in the EMP that references this data is automatically assigned, which is the function of the on delete set NULL.

Use on delete set NULL one thing to note is that the column referenced by the other table must be able to be empty and not have a NOT NULL constraint, and for the above example the Dept column must not have a NOT NULL constraint, if the NOT NULL constraint is already defined, When you use the on delete set NULL to delete the referenced data, it will occur: ORA-01407: Unable to update ("DD". " EMP "." DEPT ") is a null error.

In general, the effect of on DELETE cascade and on delete set NULL is to handle cascading delete problems, and if you need to delete data that is referenced by other data, then you should decide exactly what you want Oracle to do with those data that are about to be deleted. You can have three different ways:
Disable deletion, which is also the Oracle default
Empty the corresponding column of data that references this value, which requires the use of the on delete set NULL keyword
Delete the data that references this value, which requires the use of the ON DELETE cascade keyword

2. Oracle Delete User times "must specify CASCADE to remove ' SE '"

This means that you want to delete the Oracle user "se" under the database objects, such as table, view, etc., so that you delete the user must add the option Cascade:drop user SE cascade; Indicates that user SE is deleted and all data objects under the SE user are deleted. Another way is to delete all the data objects under SE, so that se becomes a null user with nothing, then drop user se; 3.ORACLE DROP table cascade constraintsWhen you drop a table, deleting the table action causes trigger or constraint to conflict, and the system will receive an error warning message without allowing execution. A very simple example, such as you have an employee base table, which may have employee number and employee name fields, and an employee Sales table with employee number and employee Sales two fields, employee Salary table employee Number field is a foreign key reference to employee base table employee number :
sql> drop table t;
Table dropped.
sql> drop table T1;
Table dropped.
Sql> CREATE TABLE t (ID number,name varchar2 (20));
Table created.
sql> CREATE TABLE t1 (ID number,sal number);

Table created.
Sql> ALTER TABLE t add constraint T_PK primary key (ID);
Table altered.
Sql> ALTER TABLE t1 ADD constraint T_FK foreign key (ID) references T (ID);
Table altered.
Sql> INSERT INTO t values (1, #39; jack& #39;);
1 row created.
Sql> INSERT INTO t values (2, #39; mary& #39;);
1 row created.
Sql> COMMIT;
Commit complete.
sql> INSERT INTO T1 values (1,1000);
1 row created.
sql> INSERT INTO T1 values (2,1500);
1 row created.
Sql> commit;
sql> INSERT into T1 values (3,200);
INSERT into T1 values (3,200)
*
ERROR at line 1:
Ora-02291:integrity constraint (SYS. T_FK) violated-parent key not found
(Against the constraint, the Staff basic information table there is no 3 this employee, how to sell the record. )

sql> drop table t;
drop table T
*
ERROR at line 1:
ora-02449:unique/primary keys in table referenced by foreign keys
(in violation of constraint, the Employee sales form T1 has a reference to table T, this reference relation does not allow you to drop table T)
sql> DROP TABLE t cascade constraints;
Table dropped.
Sql> select * from T1;
ID SAL
---------- ----------
1 1000
2 1500
Sql> Select Constraint_name,table_name from dba_constraints where wner = #39; sys& #39; and table_name = & #39; t1& #39;
No rows selected
Sql>
We can find that the DROP table cascade constraints can be used to delete the constraint of the associated table T to achieve the purpose of your drop table T, which originally belonged to T1 's foreign key Constraint has been removed, but the data stored in table T1 will not be deleted, meaning that the DROP table cascade constraints does not affect the objec stored in RowData.

Cascade Introduction and Usage (Oracle)