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 ).

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 mapping SCOTT. FOREIGN_SUB to SCOTT. FOREIGN_SUB OCI Error ORA-01403: no data found, SQL .

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.