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_AMO UNT_RECEIP T) 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_AMOUNtablePRO D" FOREIGN KEY ("CITATION_AMOUNT_ID")
REFERENCES "seal_prod"."T_CITATION_AM OUNT" ("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"."P AYMENT_MET HOD" IS 'C Cash, K Check, R Credit Card, O Money Order';
COMMENT ON COLUMN "seal_prod"."tablePROD"."C ITATION_AM OUNT_ID" IS 'FK to t_citation_amount table';
COMMENT ON COLUMN "seal_prod"."tablePROD"."V OIDED" 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.next val
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_DLU 1"
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_DLU 1" DISABLE;
Please help me save my DBA career.
Thanks.
Willie
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_AMO
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_AMOUNtablePRO
REFERENCES "seal_prod"."T_CITATION_AM
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"."P
COMMENT ON COLUMN "seal_prod"."tablePROD"."C
COMMENT ON COLUMN "seal_prod"."tablePROD"."V
CREATE OR REPLACE TRIGGER "seal_prod"."tablePROD_ID"
before insert on tablePROD
for each row
declare
-- local variables here
begin
select sysdate,tablePROD_seq.next
into :new.dlu, :new.receipt_id
from dual;
end tablePROD_id;
/
ALTER TRIGGER "seal_prod"."tablePROD_ID"
CREATE OR REPLACE TRIGGER "seal_prod"."tablePROD_DLU
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_DLU
Please help me save my DBA career.
Thanks.
Willie
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
HuaMinChen,
Please explain how what you posted is any different than what I posted?
Please explain how what you posted is any different than what I posted?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_AM OUNT" ("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
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
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_AM
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.
You have to check the available values first, by referring to the table being referenced, for that FK column.