How to create a foreign key in Oracle in a Non-default way

If you use the Oracle default creation method when you create a foreign key constraint, it cannot be deleted when the referenced data is deleted, which gives us more flexibility in oracle9i, but we use on DELETE cascade and on delete set Null keyword to decide whether to delete the referenced data by deleting the data that references the data, or to empty the corresponding value of the data that references the data.

For example, the employee's basic information and the company's departmental information are stored separately in the following two tables. us to

create　table　dept
(deptno　number(10)　not　null,
deptname　varchar2(30)　not　null,
constraint　pk_dept　primary　key(deptno));
和
create　table　emp
(　empno　number(10)　not　null,
fname　varchar2(20)　,
lname　varchar2(20)　,
dept　number(10)　,
constraint　pk_emp　primary　key(empno));

Then we now use these two keywords to increase the foreign key to try, first let's try on 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,’销售部’);
insert　into　dept　values(2,’财务部’);
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'm going to delete the sales department, what's the consequence?

delete　from　dept　where　deptno　=　1;

We found that in addition to one of the data in the dept, two of the EMP data was deleted, with two of the data in the EMP referring to the sales department, which made it easy to understand on DELETE cascade.

Let's take a look at the delete set NULL, as the name implies, the foreign KEY constraint established in this way, and when the referenced data is deleted, the corresponding value of the data that references that data will be null, and we'll try to prove the on delete set NULL effect by experiment:

First recover the few 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;

And then we're doing the delete operation:

delete　from　dept　where　deptno　=　1;

You will also find that the value of the Dept of the two data that references this data in the EMP is automatically emptied except for the Sales Department in Dept, which is the effect of 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, and if the NOT NULL constraint is already defined, When you use the on delete set NULL to delete the referenced data, it occurs: ORA-01407: Cannot update ("DD".) EMP "." DEPT ") is a null error.

In general, the effect of the on DELETE cascade and on delete set NULL is used to handle cascading deletions, and if you need to delete data that is referenced by other data, then you should decide exactly what Oracle wants to do with the data that is about to be deleted. You can have three different ways:

Delete is prohibited. This is also the Oracle default

Empty the corresponding column that references the data for this value, which requires the on delete set NULL keyword

Delete the data that references this value, which requires the ON DELETE CASCADE keyword