ORA-02291父键未找到错误求助：临时表导入事实表失败

Let's break down this ORA-02291 error first—this message tells us that one or more foreign key values you're trying to insert into DW_ITEMS7364 don't exist in their corresponding parent tables (the DW_* dimension tables). Even though you checked data types and key order, there are a few critical angles to explore here.

Key Observations from Your Code #

First, let's flag a critical mismatch that's likely driving the error:

• Your temporary table TEMP_TAB7364 pulls data directly from source tables (MANUFACTURER7364, WAREHOUSE7364, STOCKITEM7364).
• But your fact table DW_ITEMS7364 has foreign keys pointing to data warehouse dimension tables (DW_MANUFACTURER7364, DW_WAREHOUSE7364, DW_STOCKITEM7364).

If the DW_* tables don't contain all the key values present in your source tables, the insert will fail with the "parent key not found" error.

Step-by-Step Troubleshooting #

1. Identify Which Foreign Key Is Failing #

First, let's pinpoint exactly which constraint is violated. Run this query to map the system-generated constraint name to your foreign key:

SELECT constraint_name, table_name, r_constraint_name, constraint_type
FROM user_constraints 
WHERE constraint_name = 'SYS_C007167';

Then look up the parent table for the referenced constraint (r_constraint_name):

SELECT table_name, column_name
FROM user_cons_columns 
WHERE constraint_name = '<your_r_constraint_name>';

This will tell you if the issue is with ManID, WHID, or STKID.

2. Find Mismatched Key Values #

Once you know which foreign key is problematic, run a query to find rows in TEMP_TAB7364 where the key doesn't exist in the corresponding DW_* table. For example, if it's ManID:

SELECT t.ManID, COUNT(*) AS missing_count
FROM TEMP_TAB7364 t
LEFT JOIN DW_MANUFACTURER7364 dwm ON t.ManID = dwm.ManID
WHERE dwm.ManID IS NULL
GROUP BY t.ManID;

Repeat this for WHID and STKID by swapping in the relevant tables.

3. Check for Hidden Data Type Issues #

Even if you confirmed data types match, CHAR(5) columns can cause hidden problems—they pad values with spaces to reach the fixed length. If your DW_* tables use VARCHAR(5) instead (or vice versa), trailing spaces will break matches. Verify the data types and lengths:

SELECT table_name, column_name, data_type, char_length
FROM user_tab_columns 
WHERE table_name IN ('DW_MANUFACTURER7364', 'DW_WAREHOUSE7364', 'DW_STOCKITEM7364', 'TEMP_TAB7364')
AND column_name IN ('MANID', 'WHID', 'STKID');

Fixes to Resolve the Error #

Option 1: Sync Dimension Tables First #

If the DW_* tables are missing key values from your source tables, populate them before inserting into the fact table. For example, to sync manufacturers:

INSERT INTO DW_MANUFACTURER7364 (ManID, ManName, CityID)
SELECT ManID, ManName, CityID
FROM MANUFACTURER7364
WHERE ManID NOT IN (SELECT ManID FROM DW_MANUFACTURER7364);

Repeat this for DW_WAREHOUSE7364 and DW_STOCKITEM7364.

Option 2: Clean Up Key Values (If Space Issues Exist) #

If trailing spaces are causing mismatches between CHAR and VARCHAR columns, use TRIM() when inserting:

INSERT INTO DW_ITEMS7364 (ManID, WHID, STKID, Profit)
SELECT TRIM(ManID), TRIM(WHID), TRIM(STKID), Profit
FROM TEMP_TAB7364;

Option 3: Adjust the Temporary Table Query #

If your DW_* tables are intended to be the source for fact table keys, modify the temp table to pull directly from them instead of the original source tables (assuming they're aligned):

CREATE TABLE TEMP_TAB7364 AS(
    SELECT dwm.ManID, dww.WHID, dws.STKID, (dws.SellingPrice - dws.PurchasePrice) AS "Profit"
    FROM DW_MANUFACTURER7364 dwm
    LEFT JOIN DW_STOCKITEM7364 dws ON dws.ManID = dwm.ManID
    RIGHT JOIN DW_WAREHOUSE7364 dww ON dws.WHID = dww.WHID
    WHERE dws.SELLINGPRICE IS NOT NULL AND dws.PURCHASEPRICE IS NOT NULL
);

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