插入数据时遇ORA-02291错误，求解决（附ATTRACTION_MH表结构）

Hey Matt, let's break down that ORA-02291 error you're hitting when adding data to your attraction table.

First, let's demystify the error: ORA-02291 means you're violating a foreign key constraint—the value you're trying to insert into a child table (here, ATTRACTION_MH) doesn't exist as a primary key in its linked parent table.

Looking at your ATTRACTION_MH structure, the most likely culprit is the CityName field. It's marked as NOT NULL, and there's almost certainly a foreign key constraint tying it to a parent table (like a CITIES or CITY_MH table, where CityName serves as the primary key). When you try to insert an attraction with a CityName that isn't already present in that parent table, Oracle throws this error because it can't verify the city is a valid, existing entry.

Here's how to fix this step-by-step: #

• Step 1: Identify the exact foreign key constraint
  Run this query to find out which parent table CityName is linked to:

  SELECT constraint_name, r_constraint_name, table_name, r_table_name
  FROM user_constraints
  WHERE table_name = 'ATTRACTION_MH' AND constraint_type = 'R';
  

  The r_table_name column will show you the parent table's name (e.g., CITY_MH).

• Step 2: Check valid city values
  Query the parent table to see all existing city names:

  SELECT CityName FROM [PARENT_TABLE_NAME]; -- Replace with the r_table_name from Step 1
  

  Compare this list to the CityName value you're trying to insert. If your value isn't here, that's the root of the problem.

• Step 3: Fix the data
  You have two straightforward options:

  1. Add the missing city to the parent table first (insert a record with that CityName into the parent table), then proceed to insert your attraction.
  2. Adjust the CityName in your attraction insert to match one that already exists in the parent table.

Quick extra reminders about your table's other constraints: #

• AttractionNo must be between 1 and 5 (thanks to your CHECK constraint) — don't overlook this when setting attraction IDs.
• AttractionName has a unique constraint (UC_Attraction), so you can't have duplicate names for different attractions.

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