Shulou(Shulou.com)06/01 Report--

Relational database is based on data table and relation. The data table organizes the data in DBMS with two-dimensional relationship, while the relationship establishes the association between the data tables and builds the real object model. The primary foreign key is a constraint object that needs to exist in any database system, which is abstracted from the business logic in the object model and implemented in the database as a part of physical design.

Oracle foreign keys are an important means to maintain referential integrity, and in most cases, foreign keys are closely related. The function of the foreign key constraint is to ensure that the values of one field of the word table all correspond to the primary key field of another data table. That is, unreferenced values are not allowed in the word list column as long as the foreign key constraint exists and is valid. Specifically in the Oracle database, there are some operation options for foreign key constraints. This article mainly starts with the experiment and introduces the common operation options.

1. Environmental introduction

The author chooses Oracle 11gR2 to test, the specific version number is 11.2.0.4.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for 64-bit Windows: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

Create data tables Prim and Child, corresponding to data insertion.

SQL > create table prim (v_id number 3, v_name varchar2 100)

Table created

SQL > alter table prim add constraint pk_prim primary key (v_id)

Table altered

SQL > create table child (c_id number (3), v_id number (3), c_name varchar2)

Table created

SQL > alter table child add constraint pk_child primary key (c_id)

Table altered

Default foreign key behavior

First, let's look at the default foreign key behavior.

SQL > alter table CHILD

2 add constraint FK_CHILD_PRIM foreign key (V_ID)

3 references prim (V_ID)

4

In the absence of additional parameters, Oracle foreign keys will work strictly in the same way as standard foreign keys.

-- forcibly delete the master table record when there are child records

SQL > delete prim where v_id=2

Delete prim where v_id=2

ORA-02292: violation of full constraint (A.FK_CHILD_PRIM)-child record found

-- change the master table record if there is a child table record

SQL > update prim set v_id=4 where v_id=2

Update prim set v_id=4 where v_id=2

ORA-02292: violation of full constraint (A.FK_CHILD_PRIM)-child record found

-- modify child table records

SQL > update child set v_id=5 where v_id=2

Update child set v_id=5 where v_id=2

ORA-02291: violation of full constraint (A.FK_CHILD_PRIM)-parent keyword not found

The above experiment shows that under the default Oracle foreign key configuration, as long as the child table record exists, the master table record is not allowed to be modified or deleted. Child table records must also ensure referential integrity at all times.

III. On delete cascade

For application developers, it is troublesome to have strict foreign key constraints. If you manipulate the database records directly, it means that you need to manually handle the master and child table relationships and deal with the deletion order. On delete cascade allows a function of "delete the main table first, and then delete the records of the child table" while ensuring the overall referential integrity of the data table.

To create an on delete cascade foreign key, you only need to add the corresponding clause to the creation of the foreign key.

SQL > alter table child add constraint FK_CHILD_PRIM foreign key (v_id) references prim (v_id) on delete cascade

Table altered

Test:

SQL > delete prim where v_id=2

1 row deleted

SQL > select * from prim

V_ID V_NAME

1 kk

3 iowkd

SQL > select * from child

C_ID V_ID C_NAME

1 1 kll

2 1 ddkll

3 1 43kll

SQL > rollback

Rollback complete

The operation to delete the main table was successful, and the corresponding child table records were automatically deleted. But other operations are still not allowed.

SQL > update prim set v_id=4 where v_id=2

Update prim set v_id=4 where v_id=2

ORA-02292: violation of full constraint (A.FK_CHILD_PRIM)-child record found

SQL > update child set v_id=5 where v_id=2

Update child set v_id=5 where v_id=2

ORA-02291: violation of full constraint (A.FK_CHILD_PRIM)-parent keyword not found

On delete cascade, known as "cascading deletion", is a convenient strategy for developers to directly "ignore" child records and delete master records. However, in general, database designers and DBA generally do not recommend such a strategy.

The reason is still due to the system business rules. It is true that On delete cascade is convenient to some extent, but this automation can be risky in some business systems. For example, there is a parameter reference relationship in a system that is referenced to a master record such as a contract. According to business rules, if this parameter is referenced, it should not be deleted. If we set the on delete cascade foreign key, the associated contract records will be automatically "killed". In general, colleagues who develop parameter modules do not have enough "awareness" to make manual decisions. Based on this factor, we recommend the default strong constraint association, at least without data loss.

IV. On Delete Set Null

In addition to deleting records directly, Oracle provides a strategy to retain child table records. Note: the foreign key constraint itself does not limit the problem that the field is empty. If a foreign key is set to on delete set null, when the master table record is deleted, the master table record will be deleted and the corresponding column of the child table will be emptied, regardless of whether there is a child table corresponding record or not.

SQL > alter table child drop constraint fk_child_prim

Table altered

SQL > alter table child add constraint FK_CHILD_PRIM foreign key (v_id) references prim (v_id) on delete set null

Table altered

Delete the master table record.

SQL > delete prim where v_id=2

1 row deleted

SQL > select * from prim

V_ID V_NAME

1 kk

3 iowkd

SQL > select * from child

C_ID V_ID C_NAME

1 1 kll

2 1 ddkll

3 1 43kll

4 43kll

5 4ll

SQL > rollback

Rollback complete

The main table record is deleted and the foreign key column of the child table is emptied. The other constraint actions remain unchanged.

SQL > update prim set v_id=4 where v_id=2

Update prim set v_id=4 where v_id=2

ORA-02292: violation of full constraint (A.FK_CHILD_PRIM)-child record found

SQL > update child set v_id=5 where v_id=2

Update child set v_id=5 where v_id=2

ORA-02291: violation of full constraint (A.FK_CHILD_PRIM)-parent keyword not found

So, the next question is: what if the foreign key column cannot be empty?

SQL > desc child

Name Type Nullable Default Comments

--

C_ID NUMBER (3)

V_ID NUMBER (3) Y

C_NAME VARCHAR2 (100) Y

SQL > alter table child modify v_id not null

Table altered

SQL > desc child

Name Type Nullable Default Comments

--

C_ID NUMBER (3)

V_ID NUMBER (3)

C_NAME VARCHAR2 (100) Y

SQL > delete prim where v_id=2

Delete prim where v_id=2

ORA-01407: cannot update ("A". "CHILD". "V_ID") to NULL

Failed to change ~

The legendary on update cascade

On update cascade, known as "cascading update", is a type of foreign key operation in relational database theory. This type refers to: when the record of the primary table is modified (the primary key value is modified), the foreign key column value of the corresponding subtable is modified.

SQL > alter table child add constraint FK_CHILD_PRIM foreign key (v_id) references prim (v_id) on update cascade

Alter table child add constraint FK_CHILD_PRIM foreign key (v_id) references prim (v_id) on update cascade

ORA-00905: missing keyword

The on update cascade feature does not seem to be supported in the current Oracle version. Oracle elaborated on this issue in the official service: in the actual system development environment, it is relatively rare to directly modify the primary key. So maybe this feature will be supported in a future release.

VI. Conclusion

Oracle foreign key is a common type of constraint in our daily life. In the discussion of many experts and people in the industry, we often hear the debate about "using foreign keys or system coding". Generally speaking, those who support foreign key strategies are database experts and designers of "big handlers". With the help of the natural characteristics of the database, they can realize the function efficiently. Most of the people who support the system coding are "target faction" and other new personnel, and I believe that all problems can be solved with the help of the front end of the system.

The author's viewpoint of foreign key is "moderate foreign key, double verification". Foreign keys should be designed in the closest reference relationship, and the validation actions, front-end and database-side should be operated. Although the foreign key can guarantee the final security channel, it can not feed back the correct and acceptable information to the front end. Front-end development is intuitive, but it does consume energy. Therefore, to grasp moderation is an important starting point.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.