About Oracle foreign key references and goldengate

I. Preparation of knowledge

Constraints are placed in a table with the following five kinds of constraints:

NOT NULL NON-EMPTY constraint c specified column is not allowed to be null

The unique UNIQUE constraint u Specifies that there are no duplicate values in the column, or that each value in the table or a set of values will be unique

PRIMARY key PRIMARY KEY constraint p uniquely identifies each row of the table and does not allow null values, a table can have only one PRIMARY KEY constraint

FOREIGN key FOREIGN KEY constraint r a column in one table references a column in another table, so there is a dependency that can point to a column that references itself

Check condition constraint c Specifies whether the column satisfies a condition

Constraint naming rules

If you do not specify a constraint name Oracle server automatically specifies the constraint name in the format of SYS_CN, or you can manually specify the

The recommended constraint name is: Constraint type _ table name _ Column name.

Nn:not null-non-empty constraints, such as Nn_emp_sal

Uk:unique KEY UNIQUE Constraint

Pk:primary Key PRIMARY KEY constraint

Fk:foreign key FOREIGN KEY constraint

Ck:check conditional constraint

FOREIGN KEY constraints are used to maintain referential integrity from tables and primary tables, so a FOREIGN key constraint involves two tables.

FOREIGN KEY: Specifying columns in a 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 corresponding column in the child table is empty

Second, foreign key creation test

Foreign_main Main Table

Foreign_sub as from table

object_id as a foreign_sub foreign key, reference the OBJECT_ID value of the main table Foreign_main

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.

It is recommended that you use the primary key of the primary table as a foreign key, even if the primary key of the primary table should be a unique constraint field as a 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 test that does not exist in a primary table object_id from a 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

Tip The primary table data does not exist, and object_id from the table cannot be created to guarantee integrity

Third, cascade Delete 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 Dele TE Cascade;

Table altered.

The primary table foreign key field still cannot be updated individually under Cascade

sql> 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:integrity constraint (TEST. fk_fs_oid) Violated-child Record found

Cascade mode can remove foreign key fields from the primary table Data association Delete from table data

Sql> SELECT * from Foreign_sub where object_id=52010;

object_id object_name

---------- ------------------------------

52010 Idx_bjname

Sql> 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

Foreign key related common operations and reference documents

Establish a foreign key

ALTER TABLE name add constraint foreign key name foreign key (from the foreign key field of the table) references Foreign_main (primary table foreign key field);

DROP TABLE FOREIGN key

ALTER TABLE name drop constraint foreign key name;

Find table by foreign key

SELECT * from user_constraints where constraint_type= ' R ' and constraint_name=upper (' foreign Key Name ');

Find foreign keys by table

SELECT * from user_constraints where constraint_type= ' R ' and table_name=upper (' table name ');

Locate the foreign key of the table (including the name, the table name referencing the table, and the corresponding key name, which is divided into multi-step queries):

SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name = table to query

Column name of the query referencing the key of the 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;

After defining several foreign key changes in the SQL92 standard, how to handle the actions of child table records, including:

Limit restrict: This approach does not allow the operation of updates or deletions to the key values of the referenced records; null set to NULL: When the referenced data is updated or deleted, all references to its foreign key value are set to null;

Set to default: When the reference data is updated or deleted, then all references to its foreign key value are placed to a default value;

Cascade Cascade: When the reference data is updated, reference to its value is also updated, when the reference data is deleted, then the reference to its child table records are also deleted;

Do not action No action: This method does not allow the update or deletion of the referenced data. The difference is that this way of checking occurs after the statement is executed. This is how Oracle will default.

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= ' S Cott ');

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 a foreign key constraint is not disabled in Goldengate, a present error occurs

=============================================

2013-12-26 04:51:25 INFO OGG-00996 Oracle goldengate Delivery for Oracle, Rep_app.prm:REPLICAT Rep_app.

2013-12-26 04:51:25 WARNING OGG-00869 Oracle goldengate Delivery for Oracle, Rep_app.prm:OCI Error ora-02292:integrity C Onstraint (scott.fr_foreign_sub_object_id) Violated-child record found (status = 2292). DELETE from "SCOTT". " Foreign_main "WHERE" object_id =: B0.

2013-12-26 04:51:25 WARNING OGG-01004 Oracle goldengate Delivery for Oracle, rep_app.prm:Aborted grouped to 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).

2013-12-26 04:51:25 WARNING OGG-01003 Oracle goldengate Delivery for Oracle, rep_app.prm:Repositioning to RBA 17426 in SE Qno 29.

2013-12-26 04:51:25 WARNING OGG-01154 Oracle goldengate Delivery for Oracle Rep_app.prm:SQL Error 2292 Mapping Eign_main to SCOTT. Foreign_main OCI Error ora-02292:integrity constraint (scott.fr_foreign_sub_object_id) violated-child record found (STA tus = 2292). DELETE from "SCOTT". " Foreign_main "WHERE" object_id =: B0.

2013-12-26 04:51:25 WARNING OGG-01003 Oracle goldengate Delivery for Oracle, rep_app.prm:Repositioning to RBA 17426 in SE Qno 29.

2013-12-26 04:51:25 ERROR OGG-01296 Oracle goldengate Delivery for Oracle, Rep_app.prm:Error mapping from SCOTT. Foreign_main to SCOTT. Foreign_main.

2013-12-26 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 (Permission denied).

2013-12-26 04:51:25 ERROR OGG-01668 Oracle goldengate Delivery for Oracle, Rep_app.prm:PROCESS abending.

2013-12-26 04:52:20 INFO OGG-00996 Oracle goldengate Delivery for Oracle, Rep_app.prm:REPLICAT Rep_app.

2013-12-26 04:52:20 WARNING OGG-00869 Oracle goldengate Delivery for Oracle Rep_app.prm:No The unique key is defined for tab Le ' foreign_sub '. All viable columns would be used to represent the key, and but may not guarantee uniqueness. Keycols May is used to define the key.

2013-12-26 04:52:20 WARNING OGG-01004 Oracle goldengate Delivery for Oracle, rep_app.prm:Aborted grouped to transaction on ' SCOTT. Foreign_sub ', Database error 1403 (OCI error ora-01403:no data found, SQL ).

2013-12-26 04:52:20 WARNING OGG-01003 Oracle goldengate Delivery for Oracle, rep_app.prm:Repositioning to RBA 17426 in SE Qno 29.

2013-12-26 04:52:20 WARNING OGG-01154 Oracle goldengate Delivery for Oracle Rep_app.prm:SQL Error 1403 mapping Eign_sub to SCOTT. Foreign_sub OCI Error ora-01403:no data found, SQL .

2013-12-26 04:52:20 WARNING OGG-01003 Oracle goldengate Delivery for Oracle, rep_app.prm:Repositioning to RBA 17426 in SE Qno 29.

2013-12-26 04:52:20 ERROR OGG-01296 Oracle goldengate Delivery for Oracle, Rep_app.prm:Error mapping from SCOTT. Foreign_sub to SCOTT. Foreign_sub.

2013-12-26 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 (Permission denied).

2013-12-26 04:52:20 ERROR OGG-01668 Oracle goldengate Delivery for Oracle, Rep_app.prm:PROCESS abending.