Can a user defined key be created for a table that already has a primary or unique key? (4297484)

Can a user defined key be created for a table that already has a primary or unique key?

Can a user defined key be created for a table that already has a primary or unique key?

General information.

It is possible to create a user defined key for a table that already has a unique or primary key. The Administrator Guide delves on this. The user defined key (surrogate key) will override the key definition of the table (the key used in table creation DDL).  Think of it as using hints in Oracle to specify a key that is different from the one defined for the table, based on user’s directive that results in the optimizer using most efficient plan due to the hint. However, if the hint is incorrect, it can result in sub-optimal performance.

Please refer to Administrator Guide section titled “Define a unique key” that delves on User defined key (also termed as surrogate key). The section has been pasted in the Additional Information section below. Though the Administrator Guide seem to suggest that the user defined key should be  be created if there is no unique or primary key, it also clarifies that the key can be created despite the presence of a unique or primary key. The onus is on users to ascertain if overriding the unique or primary key with a user defined key will serve the purpose. 

Define a unique key NOTE: Valid for Oracle tables only. Replication of SQL Server tables requires them to be defined with a primary key. If a table was not created with a primary or unique key, you can specify columns to use as a key when you specify the object in the configuration file. SharePlex uses the specified columns as a unique key in its WHERE clause to locate target rows for posting. NOTES: Without a primary or unique key, SharePlex uses all of the columns of a table (or all of the columns in a column partition) as a key, which slows replication performance. When a key definition is specified for a table that has a PRIMARY or UNIQUE key, the key definition overrides the defined key. This can be useful if you do not want any of the existing keys to be used by SharePlex. The columns that you specify as a key must meet the following criteria: They cannot be LONG or LOB columns. They must be able to uniquely identify a row. Otherwise, replication could return out-of-sync errors or post to incorrect target rows. They must be part of the column partition if the table is configured for vertically partitioned replication. When using the exclude column notation in vertical partitioning, the excluded columns cannot be used in the key definition. Include the columns in a supplemental log group. Otherwise, SharePlex must query the database for their values. Create an index on the target table and add the index to the SharePlex hints file, located in the variable- data directory, which directs the Post process to use the index.