存在外键约束时LOGGING ERRORS功能失效，如何分离有效与无效行？

Hey there! Let's tackle your problem head-on—first, let's diagnose why LOGGING ERRORS isn't working as expected with foreign key constraints, then walk through how to split valid rows into your child table and log the invalid ones.

Here are the most likely missteps that could be breaking your workflow:

• Missing Error Table: You can't log errors if the error table doesn't exist! You need to pre-create it using DBMS_ERRLOG.CREATE_ERROR_LOG—this tool generates a table with extra columns to store error details (like ORA_ERR_MESG$ for the error message). If you skipped this step, the LOGGING ERRORS clause has nowhere to write failures.
• No REJECT LIMIT (or Set to 0): By default, if any row violates a constraint, the entire insert aborts. You need to set REJECT LIMIT UNLIMITED (or a specific number of allowed failures) to let valid rows go through while invalid ones get logged. Without this, foreign key violations will stop the whole operation instead of being captured.
• Constraint Validation Timing: Most databases (like Oracle, where LOGGING ERRORS is a native feature) validate foreign keys before the error logging kicks in. If your child row references a non-existent parent, the constraint check fails immediately—unless you're using deferrable constraints and deferring validation until commit time. For non-deferrable constraints, the error triggers before logging can happen.
• Permission Gaps: Your user account might lack INSERT access to the error table, or EXECUTE privileges on DBMS_ERRLOG. Without these, the logging process fails silently (or throws a permission error that halts the insert).
• Incorrect Error Table Mapping: If you manually created the error table instead of using DBMS_ERRLOG, it might not have the required columns or match the target table's structure. This causes the logging clause to fail.

Let's walk through a step-by-step solution using Oracle (since LOGGING ERRORS is most commonly used here):

Step 1: Create the Error Table #

First, generate the error table for your child table with the built-in package:

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name => 'YOUR_CHILD_TABLE', -- Replace with your child table name
    err_log_table_name => 'CHILD_TABLE_ERRORS' -- Name for your error table
  );
END;
/

This creates a table that mirrors your child table plus columns to store error metadata.

Step 2: Run the Insert with Error Logging #

Use the LOG ERRORS clause along with REJECT LIMIT UNLIMITED to allow partial inserts. Here's an example:

INSERT INTO your_child_table (child_id, parent_id, child_data)
SELECT source_child_id, source_parent_id, source_child_data
FROM your_source_table -- The table where your raw data lives
LOG ERRORS INTO child_table_errors ('INSERT_BATCH_20240520') -- Tag to identify this batch
REJECT LIMIT UNLIMITED;

The tag helps you filter errors for specific batches later.

Step 3: Review Invalid Rows #

After the insert, query the error table to see which rows failed and why:

SELECT 
  ora_err_tag$, 
  ora_err_mesg$, 
  child_id, 
  parent_id 
FROM child_table_errors
WHERE ora_err_tag$ = 'INSERT_BATCH_20240520';

ORA_ERR_MESG$ will show you the exact constraint violation (e.g., "parent_id does not exist in parent_table").

Bonus: Handling Parent/Child Batch Inserts #

If you're inserting both parent and child rows in one go, split the process to avoid foreign key issues:

1. First insert all valid parent rows (log any invalid parents if needed).
2. Then insert child rows, using LOG ERRORS to catch those referencing non-existent parents.

Alternatively, filter valid child rows upfront with a join, then log the rest manually:

-- Insert valid child rows (those with existing parents)
INSERT INTO your_child_table (child_id, parent_id, child_data)
SELECT sc.source_child_id, sc.source_parent_id, sc.source_child_data
FROM your_source_table sc
JOIN your_parent_table pt ON sc.source_parent_id = pt.parent_id
LOG ERRORS INTO child_table_errors ('VALID_CHILD_INSERTS') REJECT LIMIT UNLIMITED;

-- Insert invalid child rows into the error table
INSERT INTO child_table_errors (child_id, parent_id, child_data, ora_err_mesg$, ora_err_tag$)
SELECT 
  sc.source_child_id, 
  sc.source_parent_id, 
  sc.source_child_data, 
  'Foreign key violation: Parent ID does not exist', 
  'INVALID_CHILD_ROWS'
FROM your_source_table sc
WHERE NOT EXISTS (
  SELECT 1 FROM your_parent_table pt 
  WHERE sc.source_parent_id = pt.parent_id
);

内容的提问来源于stack exchange，提问作者RufusSC2