Use of non-default creation of Foreign keys in Oracle

This article mainly introducesOracleNon-default creation of Foreign keys is much better than the default creation method of Oracle, which can solve the inconvenience caused by the default creation method of Oracle, I hope the content involved in this article will be helpful to you.

For example, the following two tables respectively store the basic information of the employee and the department information of the company. For

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 (deptno) on delete cascade;

Add a foreign key first. Insert data.

Insert into dept values (1, 'salesman ');

Insert into dept values (2, 'Finance shell ');

Insert into emp values (2, 'Mary ', 'song', 1 );

Insert into emp values (3, 'linda ', 'Liu', 2 );

Insert into emp values (4, 'linlin', 'zhang ', 1 );

Now I want to delete the Sales Department. What are the consequences?

Delete from dept where deptno = 1;

We found that in addition to one piece of data in dept, two pieces of data in emp were also deleted. The two pieces of data in emp refer to the data in sales department, this 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 this data will become null. The following experiment demonstrates 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 on delete set null, 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 will be deleted based on the data to be deleted. There are three methods:

Delete prohibited. This is also the default Oracle

Null is assigned to the columns corresponding to the data with reference to the current value. The on delete set null keyword must be used.

Delete the data with reference to the current value. Use the on delete cascade keyword.