[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1894
  • Last Modified:

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
0
willie0-360
Asked:
willie0-360
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Not following everything you did but the error is sort of straight forward.

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.

You mentioned an export/import.  I would perform a FULL export without ROWS, then you can perform a import and create a SQL file that shows all the DDL.  You can then look in that file and see how everything is put together.

Depending if you used datapump or the classic exp, the arguments are different.

The least of your worries but FYI, this can be shortened:
CREATE OR REPLACE TRIGGER "seal_prod"."tablePROD_DLU1"
  before update on tablePROD
  for each row
declare
  -- local variables here
begin
:new.dlu := sysdate;
end tablePROD_dlu1;

Open in new window


depending on your version, you can shorten the other trigger as well.
0
 
HuaMinChenBusiness AnalystCommented:
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.
0
 
slightwv (䄆 Netminder) Commented:
HuaMinChen,

Please explain how what you posted is any different than what I posted?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
johnsoneSenior Oracle DBACommented:
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.  That is for starters, there may be more.

You really need someone on the application development side to tell you where all the data goes so that you can get everything.  Doing it piece by piece is going to be a long process.  Then if there is a constraint that is enforced by the application but not defined in the database you are going to miss data.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
As every one stated in their previous post its clear that you don't have the child records and I told totally agree with Johnsone without the table design and the functional flow its very long process to migrate the data from DEV to PROD.

check constraint for the table.
if you have DBA privileges
select table_name from DBA_CONSTRAINTS where constratint_name='FK_CITATION_AMOUNT_RECEIPT' and owner='seal_prod';

import the data's from the above table as well.

But it will be really good if you have someone from application team to let you know which are the tables gets impacted so that you and import all these tables from DEV to PROD.
0
 
willie0-360Author Commented:
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
0
 
johnsoneSenior Oracle DBACommented:
The REFERENCES clauses would indicate the integrity constraints that are generating the ORA-02291.  That is how referential integrity constraints are defined.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now