Logging error fails when there is a foreign key

Question

I can't get the LOGGING ERRORS functionality to work when there is a foreign key constraint. What am I doing wrong?

Here is my example:

/*
DROP TABLE DM_CALIDAD.AMK_ACCT_DTL_DATA;
DROP TABLE DM_CALIDAD.AMK_ACCT_DTL_ERR;
DROP TABLE DM_CALIDAD.AMK_ACCT_DTL;
DROP TABLE DM_CALIDAD.AMK_ACCT_ANC;
*/

-- Create parent table 
CREATE SET TABLE DM_CALIDAD.AMK_ACCT_ANC ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ACCT_ID INTEGER NOT NULL,
      LOAD_TS TIMESTAMP(6) NOT NULL)
UNIQUE PRIMARY INDEX ACCT_ANC_UPI ( ACCT_ID );

-- Create child table
CREATE SET TABLE DM_CALIDAD.AMK_ACCT_DTL ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ACCT_ID INTEGER NOT NULL,
      ACCT_DTL_ID INTEGER NOT NULL,
      LOAD_TS TIMESTAMP(6) NOT NULL, 
FOREIGN KEY ( ACCT_ID ) REFERENCES DM_CALIDAD.AMK_ACCT_ANC ( ACCT_ID ))
UNIQUE PRIMARY INDEX ACCT_DTL_UPI ( ACCT_ID ,ACCT_DTL_ID );

-- Create data table to use with insert/select. Same as child, but no FK
CREATE SET TABLE DM_CALIDAD.AMK_ACCT_DTL_DATA ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ACCT_ID INTEGER NOT NULL,
      ACCT_DTL_ID INTEGER NOT NULL,
      LOAD_TS TIMESTAMP(6) NOT NULL)
UNIQUE PRIMARY INDEX ACCT_DTL_DATA_UPI ( ACCT_ID ,ACCT_DTL_ID );

-- Create error table
CREATE ERROR TABLE DM_CALIDAD.AMK_ACCT_DTL_ERR FOR DM_CALIDAD.AMK_ACCT_DTL;

--Insert data on parent and data tables.
INSERT INTO DM_CALIDAD.AMK_ACCT_ANC VALUES(1, current_timestamp);
INSERT INTO DM_CALIDAD.AMK_ACCT_DTL_DATA VALUES(1, 1, current_timestamp);
INSERT INTO DM_CALIDAD.AMK_ACCT_DTL_DATA VALUES(2, 1, current_timestamp); -- This will fail

--Insert/select into the child table
INSERT INTO DM_CALIDAD.AMK_ACCT_DTL 
  SELECT * FROM DM_CALIDAD.AMK_ACCT_DTL_DATA 
  LOGGING ALL ERRORS WITH NO LIMIT;

-- The insert/select fails with the next error
--STATEMENT 1: Insert Statement failed.  Failed [9127 : HY000] Index violations detected; errors logged in AMK_ACCT_DTL_ERR where ETC_dbql_qid = 307181783828932305.

How can I insert the valid rows into the child table and the invalid ones into the error table?

Thanks!

Answer 1

Logging Errors doesn't support FK violations, see the manuals for Logging Errors For INSERT … SELECT Requests

When Teradata Database encounters USI or RI errors (or both) in the INSERT … SELECT operation, the following events occur in sequence:
1. The transaction or request runs to completion.
2. The system writes all error-causing rows into the error table.
3. The system aborts the transaction or request.
4. The system rolls back the transaction or request.