Several operation options for Oracle Foreign Key constraint (Foreign Key)

Several operation options for Oracle Foreign Key constraint (Foreign Key)

Relational databases are based on data tables and relationships. A data table organizes data in a DBMS based on a two-dimensional relationship, establishes associations between data tables, and builds a realistic object model. The primary and Foreign keys are constraints that must exist in any database system. They are abstracted from the business logic in the object model and implemented in the database as part of the physical design.

Oracle Foreign keys are an important means to maintain the integrity of the reference. In most cases, foreign keys are closely related. The foreign key constraint ensures that all values of a field in the Word Table correspond to the primary key fields in another data table. That is to say, as long as the foreign key constraint exists and is valid, no reference value is allowed to appear in the column. In the Oracle database, foreign key constraints still have some operation options. This article describes common operation options starting with an experiment.

1. Environment Introduction

I chose Oracle 11gR2 for testing. The specific version 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, and insert corresponding data.

SQL & gt; 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 (100 ));

Table created

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

Table altered

Ii. Default foreign key Behavior

First, check the default foreign key behavior method.

SQL> alter table CHILD

2 add constraint FK_CHILD_PRIM foreign key (V_ID)

3 references prim (V_ID)

4;

Oracle Foreign keys work strictly in the standard foreign key mode without adding additional parameters.

-- In the case of subrecords, the master table records are forcibly deleted;

SQL> delete prim where v_id = 2;

Delete prim where v_id = 2

ORA-02292: violation of the complete constraints (A. FK_CHILD_PRIM)-subrecord found

-- Modify the primary table record if a sub-Table record exists;

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 the complete constraints (A. FK_CHILD_PRIM)-subrecord found

-- Modify sub-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: violating the complete constraint condition (A. FK_CHILD_PRIM)-parent keyword not found

The preceding experiment shows that the primary table records cannot be modified or deleted as long as a sub-Table record exists in the default Oracle foreign key configuration. The integrity of the subtable records must also be guaranteed at all times.

III. On delete cascade

For application developers, strict foreign key constraints are troublesome. If you directly operate on database records, it means you need to manually process the relationship between the master table and sub-tables to solve the deletion order problem. On delete cascade allows the "delete a master table first and then delete sub-table records" function, while ensuring the overall integrity of the data table.

To create an on delete cascade foreign key, you only need to add the corresponding clause to the create 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 kll

2 1 ddkll

3 1 43kll

 

SQL> rollback;

Rollback complete

The primary table is deleted successfully, and corresponding sub-table records are deleted automatically. However, other operations are 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 the complete constraints (A. FK_CHILD_PRIM)-subrecord found

 

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

Update child set v_id = 5 where v_id = 2

ORA-02291: violating the complete constraint condition (A. FK_CHILD_PRIM)-parent keyword not found

On delete cascade is called "Cascading deletion". It is a convenient policy for developers to directly "Ignore" sub-records and delete the primary records. However, this policy is generally not recommended by database designers and DBAs.

The reason is determined by the system business rules. On delete cascade is indeed very convenient to some extent, but this automatic operation may be risky in some business systems. For example, a system has a parameter reference relationship, which is referenced in a master record such as a contract. According to business rules, if this parameter has been referenced, it should not be deleted. If we set the on delete cascade foreign key, the associated contract records will be automatically "killed. Colleagues who develop parameter modules generally do not have enough "Awareness" to make manual judgments. Based on this factor, we recommend that you use the default Strong Constraint association, at least not to cause data loss.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Install Oracle 11.2.0.4 x64 in Oracle Linux 6.5

For more details, please continue to read the highlights on the next page:

1 2 Next Page