Limitations

Several conditions limit when, or how, the upsert scripts may be used.

Quoted Identifiers

Schema, table, and column names must not require quoting.

Check Constraints

Check constraints on tables are not checked by the upsert scripts.

Reserved Table and View Names

The upsert scripts use temporary tables and views while they are running. The names of all of these objects start with the prefix “ups_”. If the application that calls these scripts contains or uses tables or views with the same prefix, there is a potential for conflict. If the database’s base tables or views use this prefix, there is a potential for data loss.

MariaDB/MySQL does not support temporary views, or views on temporary tables, so the ‘temporary’ tables and views that the scripts use while running are created as permanent database objects, and then dropped when the scripts terminate normally. If the scripts terminate abnormally (e.g., because of a data type mismatch between base and staging tables), those ‘temporary’ tables and views will remain in the database. They will be removed the next time that the scripts terminate normally, however.

Primary Key Requirements

Staging tables must contain all of the primary key columns of the corresponding base table, and those columns must be populated with legitimate values. The upsert scripts compare primary key values in the staging and base tables to determine which rows of the base table to update, and which rows of the staging table to add to the base table. If the base table has an autonumber or identity column as its primary key, appropriate values of that column must be assigned to rows of the staging table before the upsert scripts are run.

Updates to Primary Key Columns

Because the upsert scripts compare the primary key values in base and staging tables, they cannot be used to update primary key values in a base table.

Multi-Table Referential Cycles

When ordering tables so that the upsert operation can be performed top-down (i.e., updating parents before children), the upsert script will break multi-table referential cycles that involve two or more tables. An example of such a cycle would be a relationship such as:

• Table A is a parent of Table B
• Table B is a parent of Table C
• Table C is a parent of Table A.

If such a structure exists, the upsert script will include all of the tables, but the order in which they are processed for foreign key checks, and the order in which upsert operations occur, will be arbitrary. Whether or not this arbitrary order will cause a problem depends on the specific data in the base tables and in the staging tables.

Multi-Schema Referential Constraints

When ordering tables for the upsert operation, the scripts evaluate foreign key constraints only among tables in the base schema. If any tables in the base schema have any referential constraints to tables in other schemas, the upsert operations may not be performed in the correct order.