Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02291: integrity constraint (oralce 9.0)
ORA-02291: integrity constraint [message #422828] Sun, 20 September 2009 03:09 Go to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
When I run the insert command .
INSERT INTO BS_COMPONENT_PARAMETER (N_COMP_ID, C_PARAM_GROUP, C_PARAM_NAME, C_PARAM_VALUE) VALUES ('7','CHANNEL','RETRY_NUMBER','0');

I get below error after commit ;
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (FRWKADMIN.FK_COMPONENT_PARAMETER) violated -
parent key not found

What is wrong in my command and table and index ??
How I can find out which index is the table link ???
How to fix the error ??
I do not know much about table structure .Please advice .

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BS_ACCOUNT TABLE
BS_ACCOUNT_PROFILE TABLE
BS_AUDIT_TRAIL TABLE
BS_AUDIT_TRAIL_ACTION TABLE
BS_AUDIT_TRAIL_FUNCTION TABLE
BS_AUDIT_TRAIL_OBJ_TYPE TABLE
BS_AUDIT_TRAIL_VIEW VIEW
BS_COMPONENT TABLE
BS_COMPONENT_PARAMETER TABLE
BS_ERROR_CODE_VIEW VIEW
BS_INVOCATION TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BS_PRODUCT_CONFIG_DATA TABLE
BS_PRODUCT_DOMAIN TABLE
BS_PRODUCT_ERROR TABLE
BS_PRODUCT_TYPE TABLE
BS_PROFILE_DATA TABLE
BS_PROFILE_RIGHT TABLE
BS_SYSTEM_CONFIG_DATA TABLE
CI_DELIVERY TABLE
CI_MESSAGE TABLE
FRWK_VERSION_LEVEL TABLE
INSTALL_PRD_PARAM TABLE

22 rows selected.

Report message to a moderator

Re: ORA-02291: integrity constraint [message #422829 is a reply to message #422828] Sun, 20 September 2009 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is wrong in my command and table and index ??

Nothing wrong with the command, something wrong with your data:
Quote:
integrity constraint (FRWKADMIN.FK_COMPONENT_PARAMETER) violated - parent key not found

ORA-02291: 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


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Report message to a moderator

Re: ORA-02291: integrity constraint [message #422830 is a reply to message #422829] Sun, 20 September 2009 03:32 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I do not know much about key.index ??
Could you advice how can I search/check to fix the error ???
What index do I need to delete or re-build ???

SQL> desc BS_COMPONENT_PARAMETER;
Name Null? Type
----------------------------------------- -------- ----------------------------
N_COMP_ID NOT NULL NUMBER(10)
C_PARAM_GROUP NOT NULL VARCHAR2(40)
C_PARAM_NAME NOT NULL VARCHAR2(40)
C_PARAM_VALUE VARCHAR2(512)

Report message to a moderator

Re: ORA-02291: integrity constraint [message #422832 is a reply to message #422829] Sun, 20 September 2009 03:58 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
the index PK_BS_COMPONENT_PARAMETER cannot be drop as it is primary index ...
What can I do , Any suggestion ????
--------------------------------------------------
SQL> select table_name,index_name,column_name from user_ind_columns where table_name='BS_COMPONENT_PARAMETER';

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
BS_COMPONENT_PARAMETER PK_BS_COMPONENT_PARAMETER
C_PARAM_GROUP

BS_COMPONENT_PARAMETER PK_BS_COMPONENT_PARAMETER
C_PARAM_NAME

BS_COMPONENT_PARAMETER PK_BS_COMPONENT_PARAMETER
N_COMP_ID

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
FK_BS_ACCOUNT_PROFILE
FK_BS_AUDIT_TRAIL_DATE
FK_BS_AUDIT_TRAIL_USER
FK_BS_COMPONENT_HOST
FK_BS_INV_DEST
FK_BS_INV_RECORD_STATE
FK_BS_INV_SUBMITTOR
FK_BS_INV_USER_DATE
FK_BS_RIGHT_PROFILE
IDX_CI_DELIVERY
PK_BS_ACCOUNT

INDEX_NAME
------------------------------
PK_BS_ACCOUNT_PROFILE
PK_BS_AUDIT_TRAIL
PK_BS_AUDIT_TRAIL_ACTION
PK_BS_AUDIT_TRAIL_FUNCTION
PK_BS_AUDIT_TRAIL_OBJ_TYPE
PK_BS_COMPONENT
PK_BS_COMPONENT_PARAMETER
PK_BS_INVOCATION
PK_BS_PRODUCT_CONFIG_DATA
PK_BS_PRODUCT_DOMAIN
PK_BS_PRODUCT_TYPE

INDEX_NAME
------------------------------
PK_BS_PROFILE_DATA
PK_BS_PROFILE_RIGHT
PK_BS_SYSTEM_CONFIG_DATA
PK_CI_DELIVERY
PK_CI_MESSAGE
PK_INSTALL_PARAM
PK_PRODUCT_ERROR
SYS_IL0000005968C00005$$
SYS_IL0000005974C00012$$
SYS_IL0000005974C00013$$
UK_BS_COMP_NAME

INDEX_NAME
------------------------------
UK_UNIQUE_ACTION_NAME
UK_UNIQUE_FUNCTION_NAME
UK_UNIQUE_OBJECT_TYPE

36 rows selected.

Report message to a moderator

Re: ORA-02291: integrity constraint [message #422836 is a reply to message #422832] Sun, 20 September 2009 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not drop you primary key neither your foreign key, they are part of the model, check your code and data and fix them accordingly.

You can find contraints definition in user_constraints.

Regards
Michel

Report message to a moderator

Re: ORA-02291: integrity constraint [message #422838 is a reply to message #422836] Sun, 20 September 2009 06:07 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
>>You can find contraints definition in user_constraints.

How ??
Please advice as I do not know much table structure ....

Report message to a moderator

Re: ORA-02291: integrity constraint [message #422839 is a reply to message #422838] Sun, 20 September 2009 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either use SQL*Plus and describe command or refer to Database Reference

Regards
Michel

Report message to a moderator

Re: ORA-02291: integrity constraint [message #422846 is a reply to message #422838] Sun, 20 September 2009 14:35 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
chuikingman wrote on Sun, 20 September 2009 13:07

How ??
Please advice as I do not know much table structure ....


May I ask the question, if you don't know the table structure, why are you trying to insert data?

Usually, you look in the documentation of the software, or contact the person who wrote the software before you try to insert data into random tables.

Report message to a moderator

Previous Topic: Need help in sql query
Next Topic: numbering partition numbers
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 19:41:47 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.