Oracle foreign key reference and goldengate

I. Preparation knowledge
Constraints are placed in tables. There are five constraints:
Not null the column specified by the non-NULL constraint C cannot be NULL.
UNIQUE has no duplicate values in the column specified by the UNIQUE constraint U, or each value in the table or each group of values will be UNIQUE.
Primary key constraint P uniquely identifies each row of the output table and does not allow null values. A table can only have one primary key constraint.
Foreign key constraint R columns in a table reference columns in other tables, so that dependency exists and can point to the column that references itself
CHECK condition constraint C specifies whether the column meets a condition
Constraint naming rules
If the constraint name is not specified, Oracle server automatically specifies the constraint name in SYS_Cn format, you can also manually specify it,
The recommended constraint name is: constraint type _ TABLE name_column name.
NN: not null non-empty constraint, such as nn_emp_sal
UK: unique key constraint
PK: primary key constraint
FK: foreign key constraint
CK: CHECK condition Constraints
The foreign key constraint is used to maintain the integrity of the reference from the table and the primary table. Therefore, the foreign key constraint involves two tables.
Foreign key: indicates the column in the child table at the table level.
REFERENCES: columns marked in the parent table
On delete cascade: When a column in the parent table is deleted, the corresponding column in the child table is also deleted.
On delete set null: the columns in the subtable are empty.
 
2. Foreign key creation Test
Foreign_main
Foreign_sub is a slave table.
Object_id is used as the foreign key of foreign_sub. For details, refer to the object_id value of foreign_main in the main table.
 
SQL> create table foreign_main as select object_id from all_objects;
Table created.
SQL> select count (*) from foreign_main;
COUNT (*)
----------
49571
SQL> create table foreign_sub as select object_id, object_name from all_objects;
Table created.
 
We recommend that you use the primary key of the primary table as the foreign key. Even if it is not the primary key of the primary table, it should be a field with a unique constraint as the foreign key.
 
SQL> alter table foreign_main add constraint pk_fsid primary key (object_id );
Table altered.
 
SQL> delete from foreign_sub where object_name = 'foreign _ main ';
1 row deleted.
 
SQL> commit;
Commit complete.
 
SQL> alter table foreign_sub add constraint fr_fssid foreign key (object_id) references foreign_main (object_id );
Table altered.
 
Insert a record that does not exist in the object_id of the primary table from the table.
SQL> insert into foreign_sub values (1, 'ts ');
Insert into foreign_sub values (1, 'ts ')
*
ERROR at line 1:
ORA-02291: integrity constraint (TEST. FR_FSSID) violated-parent key not found
The system prompts that the data in the master table does not exist. The slave table cannot create the object_id that does not exist in the master table to ensure integrity.
 
Iii. Cascade deletion Test
 
SQL> alter table foreign_sub drop constraint fk_fs_oid;
Table altered.
SQL> alter table foreign_sub add constraint fk_fs_oid foreign key (object_id) references foreign_main (object_id) on delete cascade;
Table altered.
 
The foreign key field of the master table cannot be updated separately in cascade.
 
SQL & gt; update foreign_main set object_id = 52012 where object_id = 52010;
Update foreign_main set object_id = 52012 where object_id = 52010
*
ERROR at line 1:
ORA-02292: Maid (TEST. FK_FS_OID) violated-child record found
 
In cascade mode, you can use the primary table to delete foreign key field data associations to delete table data.
 
SQL> select * from foreign_sub where object_id = 52010;
OBJECT_ID OBJECT_NAME
----------------------------------------
52010 IDX_BJNAME
 
SQL & gt; delete from foreign_main where object_id = 52010;
1 row deleted.
 
SQL> commit;
Commit complete.
 
SQL> select * from foreign_sub where object_id = 52010;
No rows selected
 
Common Operations and references related to foreign keys
 
Create a foreign key
Alter table name add constraint foreign key name foreign key (from table foreign key field) references foreign_main (main table foreign key field );
Drop table foreign key
Alter table Name drop constraint foreign key name;
Use a foreign key to find a table
Select * from user_constraints where constraint_type = 'r' and constraint_name = upper ('foreign key name ');
Foreign key retrieval through table
Select * from user_constraints where constraint_type = 'r' and table_name = upper ('table name ');
Search for the table's foreign key (including the name, referenced table name, and corresponding key name. The following is a multi-step query ):
Select * from user_constraints c where c. constraint_type = 'r' and c. table_name = table to be queried
Query the column name of the key of the referenced table:
Select * from user_cons_columns cl where cl. constraint_name = foreign key reference table key name
 
Foreign key constraint temporary disabled
Alter table name disable constraint foreign key name;
 
The SQL92 standard defines how to handle the actions of sub-table records after several foreign key changes, including:
 
Restrict: This method does not allow you to update or delete the key values of the referenced records; leave it blank Set to null: When the referenced data is updated or deleted, all foreign key values for reference are set to NULL;
Set to default: When the referenced data is updated or deleted, all foreign key values for reference are Set to a default value;
Cascade: When the referenced data is updated, its value is also updated. When the referenced data is deleted, its subtable records are also deleted;
No action: you cannot update or delete referenced data in this way. The difference between this method and the restriction method is that this method of check occurs after the statement is executed. This method is used by default in Oracle.
 
Col owner for A6
COL R_OWNER FOR A6
COL TABLE_NAME FOR A15
Select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE from user_constraints where table_name in ('foreign _ main', 'foreign _ sub ');
 
Select CONSTRAINT_NAME from user_constraints e where e. table_name = 'ims _ column' and owner = 'wsjd _ ELMS6 ';
Select B. table_name, B. column_name, A. CONSTRAINT_TYPE, C. TABLE_NAME from user_constraints a, user_cons_columns B, user_constraints C
WHERE a. constraint_name = B. constraint_name AND
A.R _ CONSTRAINT_NAME = C. CONSTRAINT_NAME
AND a. r_constraint_name IN (Select CONSTRAINT_NAME from user_constraints e where e. table_name = 'foreign _ main' and owner = 'Scott ');
Create table foreign_sub as select object_id, object_name from user_objects;
 
Create table foreign_main as select object_id from foreign_sub;
 
Alter table foreign_main add constraint pk_foreign_main_object_id primary key (object_id );
 
Alter table foreign_sub add constraint fr_foreign_sub_object_id foreign key (object_id) references foreign_main (object_id) on delete cascade;
 
Alter table foreign_sub drop constraint fr_foreign_sub_object_id;
 
Alter table foreign_sub disable constraint fr_foreign_sub_object_id;
 
If the foreign key constraint is not disabled in goldengate, the current error occurs.
========================================================== =====
04:51:25 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep_app.prm: REPLICAT REP_APP started.
04:51:25 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep_app.prm: OCI Error ORA-02292: integrity constraint (SCOTT. FR_FOREIGN_SUB_OBJECT_ID) violated-child record found (status = 2292 ). delete from "SCOTT ". "FOREIGN_MAIN" WHERE "OBJECT_ID" =: b0.
04:51:25 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Aborted grouped transaction on 'Scott. FOREIGN_MAIN ', Database error 2292 (OCI Error ORA-02292: integrity constraint (SCOTT. FR_FOREIGN_SUB_OBJECT_ID) violated-child record found (status = 2292 ). delete from "SCOTT ". "FOREIGN_MAIN" WHERE "OBJECT_ID" =: b0 ).
04:51:25 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
04:51:25 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_app.prm: SQL error 2292 SCOTT ing SCOTT. FOREIGN_MAIN to SCOTT. FOREIGN_MAIN OCI Error ORA-02292: integrity constraint (SCOTT. FR_FOREIGN_SUB_OBJECT_ID) violated-child record found (status = 2292 ). delete from "SCOTT ". "FOREIGN_MAIN" WHERE "OBJECT_ID" =: b0.
04:51:25 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
04:51:25 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Error mapping from SCOTT. FOREIGN_MAIN to SCOTT. FOREIGN_MAIN.
04:51:25 WARNING OGG-01525 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Failed to open trace output file, 'gglog-REP_APP.dmp ', error 13 (Permission denied ).
04:51:25 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_app.prm: process abending.
 
04:52:20 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep_app.prm: REPLICAT REP_APP started.
04:52:20 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep_app.prm: No unique key is defined for table 'foreign _ sub '. all viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
04:52:20 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Aborted grouped transaction on 'Scott. FOREIGN_SUB ', Database error 1403 (OCI Error ORA-01403: no data found, SQL <DELETE FROM "SCOTT ". "FOREIGN_SUB" WHERE "OBJECT_ID" =: b0 AND "OBJECT_NAME" =: b1 and rownum = 1> ).
04:52:20 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
04:52:20 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_app.prm: SQL error 1403 SCOTT ing SCOTT. FOREIGN_SUB to SCOTT. FOREIGN_SUB OCI Error ORA-01403: no data found, SQL <DELETE FROM "SCOTT ". "FOREIGN_SUB" WHERE "OBJECT_ID" =: b0 AND "OBJECT_NAME" =: b1 and rownum = 1>.
04:52:20 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
04:52:20 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Error mapping from SCOTT. FOREIGN_SUB to SCOTT. FOREIGN_SUB.
04:52:20 WARNING OGG-01525 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Failed to open trace output file, 'gglog-REP_APP.dmp ', error 13 (Permission denied ).
04:52:20 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_app.prm: process abending.