Oracle uses on delete cascade and on delete set null to create a foreign key

Oracle uses on delete cascade and on delete set null to create a foreign key. In this section, we introduce the default creation method of oracle when creating a foreign key constraint, when the referenced data is deleted, it cannot be deleted. This gives us more flexible options in oracle9i, however, we use the on delete cascade and on delete set null keywords to determine whether to delete the data referenced in the data when deleting the referenced data, or leave the corresponding values of the data that references this data item empty. Www.2cto.com, for example, the basic information of employees and the department information of the company stored in the two tables below. Create table dept (deptno number (10) not null, deptname varchar2 (30) not null, constraint pk_dept primary key (deptno )); and create table emp (empno number (10) not null, fname varchar2 (20), lname varchar2 (20), dept number (10), constraint pk_emp primary key (empno )); now we can use these two keywords to add foreign keys. First, let's try on delete cascade alter table emp add constraint fk_emp_dept foreign key (dept) references dept (de Ptno) on delete cascade; add a foreign key first. Insert data. Insert into dept values (1, 'salesman'); 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, 'linlinlin', 'zhang', 1 ); now I want to delete the Sales Department. What are the consequences? Delete from dept where deptno = 1; we found that except for one piece of data in dept, two pieces of data in emp were also deleted, the two data items in emp refer to the data in the sales department, which makes it easy to understand on delete cascade. Next, let's take a look at the on delete set null. As the name suggests, the foreign key constraint created in this method. When the referenced data is deleted, the corresponding values of the data with reference to the data will become null. Next we will test to prove the function of on delete set null: first, restore the data, and then change the constraints: alter table emp add constraint fk_emp_dept foreign key (dept) references dept (deptno) on delete set null; then we are executing the delete operation: delete from dept where deptno = 1; you will also find that in addition to the sales department in dept, the dept value of the two data items referenced in this data in emp is automatically assigned null, which is the role of on delete set null. When using the on delete set null function at www.2cto.com, you must note that the column referenced by the parameter in other tables must be null and cannot have the not null constraint, in the preceding example, the dept column in emp must not have the not null constraint. If the not null constraint is defined and the on delete set null constraint is used to delete the referenced data, will happen: ORA-01407: Unable to update ("DD ". "EMP ". "DEPT") is NULL. In general, on delete cascade and on delete set null are used to deal with cascading deletion. If the data to be deleted is referenced by other data, therefore, you should decide how you want oracle to process the data that is about to be deleted by referring to the data to be deleted. You can disable deletion in three ways. This is why oracle assigns null columns of data with reference to the current value by default. This requires the on delete set null keyword to delete the data with reference to the current value, this requires the on delete cascade keyword.