This example shows how you convert two tables with standard sequences used as primary keys and a parent-child relationship by a foreign key constraint to use the MMR-ready sequence, You can then use them in a multi-master replication system.
The tables are defined as follows:
Note
Observe the foreign key constraint between columns MMR_seq_child_tbl.parent_id and MMR_seq_tbl.id.
The tables are populated with an initial set of rows:
The resulting table content is the following:
Prior to performing the conversion, obtain the current, maximum sequence value of the sequence to convert to an MMR-ready sequence. In this example, the value is 6, as seen in the id column of table MMR_seq_child_tbl.
This value is needed to set a newly created sequence to use for the MMR-ready sequence. This value must be a large enough starting value to avoid auniqu eness conflict with the converted sequence values of the existing rows.
Converting existing standard sequence values
To convert the existing sequence values in columns MMR_seq_tbl.id, MMR_seq_child_tbl.id, and MMR_seq_child_tbl.parent_id:
Permit deferred updates to the foreign key constraint.
Create the function to perform the sequence conversion.
Change the sequence columns to data type BIGINT so they are large enough for the MMR-ready sequence.
Set the unique database identifier used by the MMR-ready sequence.
Update the primary key and foreign key values with the MMR_sequence_convert function. Perform the updates affecting the foreign key constraint in the same transaction to avoid a foreign key violation error.
Reset the foreign key constraint back to its original setting. For example:
After the conversion to the MMR-ready sequence, the table content is as follows:
The parent-child foreign key relationship between columns MMR_seq_child_tbl.parent_id and MMR_seq_tbl.id is maintained.
The primary key id values incorporate the old sequence values but are increased by the addition of the 52-bit shifted, database identifier value.
Setting up the MMR-ready sequence
Perform the steps described in Creating an MMR-ready sequence on the databases to use as primary nodes. For database MMRnode_a that contains the converted tables, create a new sequence with a starting value of 7 to avoid a primary key uniqueness conflict with the existing rows. In the original tables, the maximum used sequence value was 6. When a sequence number is transformed to an MMR-ready sequence value, the same result is returned if the same database identifier is used with the same original number.
Create the function to return the MMR-ready sequence value.
Modify the primary key columns to use the function to return the default value.
Repeat the MMR-ready sequence setup process for the other primary nodes.
Repeat the process for MMRnode_c.
Tables after initial multi-master replication system creation
Create the multi-master replication system using databases MMRnode_a, MMRnode_b, and MMRnode_c in a similar manner as described in MMR-ready sequence example.
After you create the system with the initial snapshot, MMRnode_a, MMRnode_b, and MMRnode_c all contain identical content. The following is the table content:
Subsequent row insertions and synchronization
The following rows are inserted on MMRnode_a:
The following rows are inserted on MMRnode_b:
The following rows are inserted on MMRnode_c:
After you perform a synchronization replication, there are no uniqueness conflicts. The following shows the synchronized, consistent tables in the primary nodes: