SQL Error: ORA-02291: integrity constraint

Question

I am creating a database that is trying to access values from a foreign key. I have created two following tables

CREATE TABLE Component(
    ComponentID varchar2(9) PRIMARY KEY
    , TypeID varchar2(9) REFERENCES TypeComponent(TypeComponentID)
)

INSERT INTO Component VALUES(192359823,785404309)
INSERT INTO Component VALUES(192359347,785404574)
INSERT INTO Component VALUES(192359467,785404769)
INSERT INTO Component VALUES(192359845,785404867)
INSERT INTO Component VALUES(192359303,785404201)
INSERT INTO Component VALUES(192359942,785404675)


CREATE TABLE TypeComponent (
    TypeComponentID varchar2(9) PRIMARY KEY
    , Type_Description varchar2(30) CONSTRAINT Type_Description 
        CHECK(Type_Description IN('Strap', 'Buckle', 'Stud')) NOT NULL
)

INSERT INTO TypeComponent VALUES(785404309, 'Strap')
INSERT INTO TypeComponent VALUES(785404574, 'Stud')
INSERT INTO TypeComponent VALUES(785404769, 'Buckle')
INSERT INTO TypeComponent VALUES(785404867, 'Strap')
INSERT INTO TypeComponent VALUES(785404201, 'Buckle')
INSERT INTO TypeComponent VALUES(785404675, 'Stud')

These are the two tables. Component and TypeComponent. Component is the parent entity to TypeComponent, and I am trying to run the following INSERT statement:

INSERT INTO Component VALUES(192359823,785404309)

but it is giving me the error

This is the session that I have so far in Oracle SQL dev

Answer 1

Try inserting in your TypeComponent table first, then insert into your Component table.

According to the error:

ORA-02291:    integrity constraint (string.string) 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.

This means that there is no matching key in your referenced table.

EDIT #1

For your kind information, here is a site where you can get help with all of the Oracle error codes.

http://[ora-02291].ora-code.com/

Here's the homepage for this site: http://www.ora-code.com/

Where you may replace the error code in the URL to suit the error code you're getting, and you'll access to the page for this error.

Answer 2

Please post your entire SQLPLUS session so that the error is easily reproducible.

Looks like the insert into the child table is being done before the insert into the parent table which is causing this error.

Change the order of inserts and rerun your code.

SQL> CREATE TABLE TypeComponent(
  2    TypeComponentID varchar2(9) PRIMARY KEY, 
  3    Type_Description     varchar2(30) 
  4    CONSTRAINT Type_Description CHECK(Type_Description IN('Strap', 'Buckle', 'Stud')) NOT NULL
  5  )
  6  ;

Table created.

SQL> CREATE TABLE Component(
  2    ComponentID varchar2(9) PRIMARY KEY, 
  3    TypeID varchar2(9) REFERENCES TypeComponent(TypeComponentID)
  4  )
  5  ;

Table created.

SQL> INSERT INTO Component VALUES(192359823,785404309);
INSERT INTO Component VALUES(192359823,785404309)
*
ERROR at line 1:
ORA-02291: integrity constraint (COAMGR.SYS_C002513823) violated - parent key
not found

SQL> INSERT INTO TypeComponent VALUES(785404309, 'Strap');

1 row created.

SQL> INSERT INTO Component VALUES(192359823,785404309);

1 row created.

SQL> commit;

Commit complete.

Answer 3

In component table you have

 TypeID REFERENCES TypeComponent(TypeComponentID)

And then you proceed with

INSERT INTO Component VALUES(192359823,785404309)

If you executed this first, then Oracle will throw Integrity constraint error because you are trying to insert into component table a value which is not present in TypeComponent table.

This is a problem because you have mentioned that TypeID is a foreign key, ie, the value of TypeID needs to be present in TypeComponent before inserting into Component

Answer 4

This error is present some times with the referenced key (foreign key in my case) this have errors. Please recreate the referenced key in the table.