Foreign Key Checks

Declared foreign keys are a floor, not a ceiling. A relation needs no foreign key in the database: relations an application maintains in code are declared in the configuration and archived the same way. For the foreign keys the database does declare, these checks make sure that no table outside the job references a job table, and that every foreign key between two job tables is a configured relation, paired the same way.

FK_INDEX_CHECK #

Every relation needs an index on its child whose leading columns are exactly the foreign_key columns, in any order: discovery reads the child by foreign_key, and without such an index every batch scans the table. A prefix, functional or INVISIBLE index does not count. This covers relations without a declared foreign key too.

Fix: CREATE INDEX idx_fk ON <table> (<foreign_key columns>).

FK_COVERAGE_CHECK #

A table outside the job holds a foreign key into a job table. Deleting the parent either fails (RESTRICT, NO ACTION) or changes rows GoArchive never copied (CASCADE, SET NULL), so every ON DELETE rule is fatal. Incoming keys are found by the referenced schema, so a constraint defined in another schema is detected; such a table can never join the job.

FK_COVERAGE_CHECK: Foreign key constraints not covered by relations
(fatal for any ON DELETE rule):
  - orders is referenced by: [analytics.order_snapshots (ON DELETE CASCADE)]

Fix: add the referencing table to the job if it is in the same schema and should be archived. Otherwise drop the constraint, or leave the referenced table out of the job.

INTERNAL_FK_COVERAGE #

A foreign key between two job tables is not a configured relation with the same foreign_key columns. Each line names the constraint and why:

• [no relation: … is not configured under …]: the child is not nested under that parent;
• [the relation's foreign_key is …]: the relation uses other columns;
• [it references …, which is not the PRIMARY KEY of …]: a foreign key to a parent’s unique key, which is not supported, because references lists the parent’s primary key.

A table appears once in a job, so a second foreign key from one child to the same parent over other columns cannot be configured. Unfixed, the delete would fail, or an ON DELETE CASCADE would remove child rows that were never copied.

Fix: nest each child under its parent, with foreign_key and references matching the constraint, or leave one of the two tables out of the job.

FK_PAIRING_CHECK #

The relation’s foreign_key columns match the constraint, but references pairs them with the parent’s key columns differently. A swapped composite pairing would select another parent’s children.

Fix: list references in the order that pairs with foreign_key.

SELF_FK_DELETE_RULE_CHECK #

Archive and purge only. A job table, root or child, has a foreign key to itself whose rule is ON DELETE CASCADE or SET NULL. Deleting a batch row would delete, or clear the parent of, rows of the same table outside the batch, which were never copied, and the run would report success. Each line names the constraint and its rule:

  - comments(parent_id) -> comments(id) (constraint fk_comments_parent, ON DELETE CASCADE)

RESTRICT and NO ACTION pass: a delete they block fails, and nothing is lost. Any other rule is refused.

Fix: redefine the constraint with ON DELETE RESTRICT, or define a separate copy-only job for the table. A job’s type is fixed once it has run.

FK_COVERAGE_VISIBILITY_CHECK #

Archive and purge only. GoArchive cannot prove it saw every foreign key pointing into the job, including those in schemas the account cannot read. Without that proof, an external ON DELETE CASCADE or SET NULL could change rows that were never copied.

The proof is a successful read of the server’s complete foreign-key metadata, which needs PROCESS. The state is then complete, the only one that passes. If that read fails, discovery falls back to a list filtered by the account’s privileges, and the state is unconfirmed; unknown means no proof was populated. The message says which stage failed. A query-stage failure can be privileges, connectivity or server state, so read the logged cause first.

Fix, when the logged cause is a privilege refusal: GRANT PROCESS ON *.* TO <user>. A PROCESS held through a role is accepted.