CODEWITHSUNDEEP
sqloracle-database
joseph hassan
joseph hassan

Reputation: 11

ORA-02291:INTEGRITY CONSTRAINT (SYSTEM.SYS_C007150) VIOLATED - PARENT KEY NOT FOUND

When I create the table EMPLOYEE, I made ESSN as a primary key and the SUPERSSN a foreign key from the same table with DNO as a foreign key from the dep table, when I want to insert values that show up and now I am confused.

The table contains the following:

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ENAME                                     NOT NULL VARCHAR2(30)
 ESSN                                      NOT NULL CHAR(14)
 BDATE                                              DATE
 DNO                                                NUMBER(38)
 SUPERSSN                                           CHAR(14)

in first time I used the following command line:

INSERT INTO EMPLOYEE ('JOSEPH','789456','14-DEC-1986','3','123');

then I try without DNO as that:

SQL> INSERT INTO EMPLOYEE (ENAME,ESSN,BDATE)

  2  VALUES('JOSEPH','9861050560','14-DEC-1986');
-------------------------------
INSERT INTO EMPLOYEE (ENAME,ESSN,BDATE)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.SYS_C007150) violated - parent key not
found
----------------------------

Upvotes: 1

Views: 25344

Answers (3)

user8128167
user8128167

Reputation: 7676

Here is a query you could use to check to see if the values in your foreign key table exist or not, and of course if they do not exist then they would have to be inserted to resolve the parent key violation:

SELECT E.* FROM EMPLOYEE E 
    LEFT JOIN SUPER_TABLE S
        ON E.SUPERSSN = S.SUPERSSN 
WHERE S.SUPERSSN IS NULL AND E.SUPERSSN IS NOT NULL;

Of course, that is assuming the problem is with the SUPERSSN key. If you have other foreign keys, then you may have to check those as well.

Upvotes: 0

Ausaf
Ausaf

Reputation: 11

I think the new entry(having foreign key constraint) you are entering is referring to an entry which doesnt exist

Upvotes: 1

Jacob
Jacob

Reputation: 14731

Most likely parent_key record which is SUPERSSN(assuming) must be missing in parent table. You can find that out by 

SELECT *
  FROM user_constraints
 WHERE table_name = 'EMPLOYEE'

So you need to first insert values in parent table of employees table and then insert values in child table.

To find out parent_table do as

SELECT    uc.constraint_name
         || CHR (10)
         || '('
         || ucc1.TABLE_NAME
         || '.'
         || ucc1.column_name
         || ')'
            constraint_source,
            'REFERENCES'
         || CHR (10)
         || '('
         || ucc2.TABLE_NAME
         || '.'
         || ucc2.column_name
         || ')'
            references_column
   FROM user_constraints uc, user_cons_columns ucc1, user_cons_columns ucc2
   WHERE uc.constraint_name = ucc1.constraint_name
   AND uc.r_constraint_name = ucc2.constraint_name
   AND ucc1.POSITION = ucc2.POSITION 
   AND UC.TABLE_NAME = 'EMPLOYEE'
   AND uc.constraint_type = 'R'

For more details please have a look at this.

And go through this and this as well.

Upvotes: 1

Related Questions