Link to home
Start Free TrialLog in
Avatar of willie0-360
willie0-360

asked on

SQL Error: ORA-02291: integrity constraint

Hello Experts:

I am  in the process of inserting data from one table to another.  This is the deal.  For some reason, someone redirected one of the applications at work from the production database to the development database.  Therefore, when people inserted data into the application, all of it went into the development database.

Since I did some work with the person that used to be the Oracle DBA at work, now I have become the accidental DBA.  I like it so far, but it is costing me.  

Now on to the task at hand.  I need to export such data from the development database, and import it into the production database.  I do not want everything form the development database but just that data that was inserted into the development database by mistake, and that should had been inserted into the production database.

This are all the queries I ran to create a table, let us call this tableSUB, with only the data I need:

On development database:

Schema: seal_prod
Table with data I need in the development database: tablePROD

Command I ran to create tableSUB:

CREATE TABLE seal_prod.tableSUB
AS
SELECT * FROM seal_prod.tablePROD
where abc_street_id ='abc123';


Then I did an export/import and when I try this in the production database:

INSERT INTO seal_prod.tablePROD
SELECT * FROM seal_prod.tableSUB
where abc_street_id ='abc123';


I get this error:

Error starting at line : 4 in command -
INSERT INTO seal_prod.tablePROD
SELECT * FROM seal_prod.tableSUB
where abc_street_id ='abc123
Error report -
SQL Error: ORA-02291: integrity constraint (seal_prod.FK_CITATION_AMOUNT_RECEIPT) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.


One thing I noticed is that after creating the seal_prod.tableSUB table from the CREATE TABLE command, the table is created, but it does not carry with it, at least, any triggers and indexes.  I believe this is the root of the problem, but I have no clue how to get this working.

This is the SQL for the creation of the tableSUB table:

CREATE TABLE "seal_prod"."tableSUB"
   (      "ABC_STREET_ID" VARCHAR2(10 BYTE) NOT NULL ENABLE,
      "CASE_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
      "RECEIPT_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
      "DATE_RECEIVED" DATE,
      "PAYMENT_METHOD" VARCHAR2(1 BYTE),
      "RECEIVED_FROM" VARCHAR2(50 BYTE),
      "AMOUNT_RECEIVED" NUMBER,
      "NOTES" VARCHAR2(526 BYTE),
      "DLU" DATE,
      "LUB" VARCHAR2(25 BYTE),
      "CITATION_AMOUNT_ID" NUMBER,
      "VOIDED" NUMBER NOT NULL ENABLE,
      "LOCAL_ID" VARCHAR2(25 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;



This is the one for the database where I need to do the import into:

CREATE TABLE "seal_prod"."tablePROD"
   (      "AOC_STREET_ID" VARCHAR2(10 BYTE) NOT NULL ENABLE,
      "CASE_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
      "RECEIPT_ID" VARCHAR2(25 BYTE) NOT NULL ENABLE,
      "DATE_RECEIVED" DATE,
      "PAYMENT_METHOD" VARCHAR2(1 BYTE),
      "RECEIVED_FROM" VARCHAR2(50 BYTE),
      "AMOUNT_RECEIVED" NUMBER,
      "NOTES" VARCHAR2(526 BYTE),
      "DLU" DATE,
      "LUB" VARCHAR2(25 BYTE),
      "CITATION_AMOUNT_ID" NUMBER,
      "VOIDED" NUMBER NOT NULL ENABLE,
      "LOCAL_ID" VARCHAR2(25 BYTE),
       CONSTRAINT "PK_RECEIPT" PRIMARY KEY ("AOC_STREET_ID", "RECEIPT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 7340032 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TIPS_INDX"  ENABLE,
       CONSTRAINT "UNQ_RECEIPT" UNIQUE ("RECEIPT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 4194304 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TIPS_INDX"  ENABLE,
       CONSTRAINT "CHK_RECEIPT" CHECK (voided in (0,1)) ENABLE,
       CONSTRAINT "FK_CITATION_AMOUNtablePROD" FOREIGN KEY ("CITATION_AMOUNT_ID")
        REFERENCES "seal_prod"."T_CITATION_AMOUNT" ("CITATION_AMOUNT_ID") ENABLE,
       CONSTRAINT "FK_CITATION_RECEIPT" FOREIGN KEY ("AOC_STREET_ID", "CASE_ID")
        REFERENCES "seal_prod"."T_CITATION" ("AOC_STREET_ID", "CASE_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16777216 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TIPS_DATA" ;
 

   COMMENT ON COLUMN "seal_prod"."tablePROD"."PAYMENT_METHOD" IS 'C Cash,  K Check, R Credit Card, O Money Order';
 
   COMMENT ON COLUMN "seal_prod"."tablePROD"."CITATION_AMOUNT_ID" IS 'FK to t_citation_amount table';
 
   COMMENT ON COLUMN "seal_prod"."tablePROD"."VOIDED" IS '1-TRUE 0-FALSE';
 

  CREATE OR REPLACE TRIGGER "seal_prod"."tablePROD_ID"
  before insert on tablePROD
  for each row
declare
  -- local variables here
begin
  select sysdate,tablePROD_seq.nextval
  into :new.dlu, :new.receipt_id
  from dual;
end tablePROD_id;


/
ALTER TRIGGER "seal_prod"."tablePROD_ID" DISABLE;
 

  CREATE OR REPLACE TRIGGER "seal_prod"."tablePROD_DLU1"
  before update on tablePROD
  for each row
declare
  -- local variables here
begin
  select sysdate
  into :new.dlu
  from dual;
end tablePROD_dlu1;


/
ALTER TRIGGER "seal_prod"."tablePROD_DLU1" DISABLE;



Please help me save my DBA career.

Thanks.
Willie
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The error means you are inserting one Foreign key value into the table, which does not exist within the relevant referencing table.

You have to check the available values first, by referring to the table being referenced, for that FK column.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

HuaMinChen,

Please explain how what you posted is any different than what I posted?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of willie0-360

ASKER

Hello Experts:

I believe we can close this one with a good solution.

slightw, you started leading me into the right path with your statement:

“You have a foreign key constraint FK_CITATION_AMOUNT_RECEIPT.  The row you are trying to insert is looking for some parent record that doesn't exist.

You need to find out what this constraint is and insert the parent records before inserting the child records.”


johnsone, you almost told me exactly what to do when you stated:

“Based on the foreign key constraints that are in your original post, you are going to need additional records from T_CITATION and T_CITATION_AMOUNT.”

There were two tables that I needed to insert into the seal_prod.tablePROD  table before the seal_prod.tableSUB table data was inserted properly.  These two tables are t_citation_amount and t_citation_amount_fund.  
 
I would really love if you can tell me how you came up with these two names, T_CITATION and T_CITATION_AMOUNT.   I was trying to pick this up from:

REFERENCES "seal_prod"."T_CITATION_AMOUNT" ("CITATION_AMOUNT_ID")

REFERENCES "seal_prod"."T_CITATION" ("AOC_STREET_ID", "CASE_ID")


If these REFERENCES led you to mention that I needed additional records from T_CITATION and T_CITATION_AMOUNT, please let me know since it was really a good one.


praveencpk, I tried running the statements you provided, but I kept getting errors about invalid identifiers.  I believe I am not interpreting your statement properly.  I hope that as I learn more about this, I can run it successfully in the future.  However, your statement introduced me to a “troubleshooting technique” in Oracle that I did not know about, and I believe it will help me solve this kind of issues in the future much easier.

I really want to thank all guys for the support you have provided me with.  I have solved problems with the help from all of you that share your knowledge here.  I have come here looking for help from topics ranging from Cisco switches to Oracle to SSL certificates, and so on.  


Willie
The REFERENCES clauses would indicate the integrity constraints that are generating the ORA-02291.  That is how referential integrity constraints are defined.