Customized database table requirements

If you customize your database schema by creating new tables, you must meet the several requirements to use the staging server.

  • Define a primary key or a unique index.

    The staging server functions that are based on the key. To avoid logging excessive data in the STAGLOG table, log only the key (primary key or unique index). The stage utilities use the key for compression and to find the data to be propagated. If there is no key, the stage utilities cannot work.

    Rows in staging-enabled tables must be uniquely identifiable by at most five columns: Two columns that contain strings (maximum length: 254 characters) and three columns that contain numbers (maximum length: BIGINT). If your custom table does not have uniquely identifiable rows within these restrictions, you must modify your custom database table to meet these criteria

  • A referential integrity (RI) constraint cycle cannot exist among the tables.

    The staging server always propagates the parent table before the child table. If there is an RI constraint cycle, the staging server cannot distinguish between parent and child tables.

  • The names of your customized database tables must be lowercase within the STAGLOG database table.

    If the names of your customized database tables include uppercase letters in the STAGLOG table, the staging process can fail to propagate data into your customized tables.

  • The database tables contain only configuration data.

    In a business-to-consumer scenario, configuration data is under Site Administrator control, such as catalogs and catalog entries. If a table contains operational data, a customer can change the same table in a production database after a Site Administrator copies the table to the production-ready data. This copying can cause a potential key conflict or an RI constraint violation.

    The database tables cannot contain any references to operation tables.

    The tables to be propagated should not contain any foreign key references to the primary keys of operation tables. If there is such a reference, the data cannot be restored to the product database if a customer deletes the primary key after the stagingcopy.

    An insert trigger cannot exist when two tables are being inserted into the production database.

    For any two tables that are covered by the staging server (for example, R1 and R2), a trigger to insert rows into R1 or R2 cannot exist when you are inserting data into R2 and R1 in the production database. The insert trigger creates the update in both databases and generates key problems.

  • The MEMBER table cannot have a unique index.
  • Delete restrict on the customized database tables must be used with caution.

    Delete restrict inhibits the database cleanup utility's performance. You can also experience difficulties when you are cleaning the production-ready data. Before you can clean the production-ready data, you have to manually use the database cleanup utility with the force option to clean the tables. Otherwise, cleaning the production-ready data fails.

Note: All foreign key relationships in the custom tables should specify ON DELETE CASCADE

To prepare the staging server for customized tables, refer to configuring the staging server for customized tables.