troubleshooting Question

SQL Error: ORA-02291: integrity constraint

Avatar of willie0-360
willie0-360 asked on
Oracle Database
7 Comments3 Solutions2757 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Praveen Kumar Chandrashekatr
Database Analysist Senior

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros