Oracle foreign key related constraints intermediate join deletion scheme

Cascading deletion in the constraints related to SQL and Oracle Foreign keys. If you want to delete related records in the relevant system, we need to join multiple tables to delete N, and there are also constraints between them. therefore, when creating a table, add constraints.

The details are as follows:

Oracle foreign key constraints of SQL can achieve cascading deletion and cascading update; Oracle only supports cascading deletion.

Format of SQL cascading deletion and cascading update:

 
 

  
  
CREATE TABLE A001ID INT PRIMARY KEY，NAME VARCHAR20））  
  
  
CREATE TABLE A002ID INT REFERENCES A001ID）ON DELETE CASCADE ON UPDATE CASCADE，AGE TINYINT） 
 
 

Format of Oracle cascading deletion:

 
 

  
  
CREATE TABLE A001ID INT PRIMAY KEY，NAME VARCHAR220））  
  
  
CREATE TABLE A002ID INT REFERENCES A001ID）ON DELETE CASCADE，AGE NUMBER2，0））  
  
  
CREATE TABLE groups  
  
  
(  
  
  
id VARCHAR2(16) CONSTRAINT pk_groupid PRIMARY KEY,  
  
  
name VARCHAR2(32),  
  
  
description VARCHAR2(50)  
  
  
)  
  
  
TABLESPACE userspace;  
  
  
CREATE TABLE usringrp  
  
  
(  
  
  
group_id VARCHAR2(16) CONSTRAINT fk_uing_grpid  
  
  
REFERENCES groups(id)  
  
  
ON DELETE CASCADE,  
  
  
user_id VARCHAR2(16)  
  
  
)  
  
  
TABLESPACE userspace;  
  
  
PowerDesigner  
 
 

Integrity constraints

Restrict ). Modification or deletion operations are not allowed. If the primary key of the primary table is modified or deleted, if the Sub-table contains a sub-record, an error message is displayed. This is the default integrity settings.

Set Null ). If the Oracle foreign key column is allowed to be empty, if you modify or delete the primary key of the master table, set the foreign key column referenced in the child table to NULL ).

Set Default ). If the Default value is specified, if the primary key of the master table is modified or deleted, set the Oracle foreign key referenced in the sub-table to the Default value ).

Cascade ). When the primary key of the primary table is changed to a new value, the Oracle foreign key value of the subtable is modified accordingly; or when the primary key record of the primary table is deleted, delete the records of Foreign keys in the sub-table.

Note: constraint created in Oracle includes primay key and foreign key. to modify the constraint, you must first alter table drop contraint con_name; then add constraint again.