ORA-02291: integrity constraint (%%%) violated - parent key not found

From: Victor M <eh936_NO_SPAM__at_hotmail.com>
Date: Mon, 07 Jul 2003 19:07:30 GMT
Message-ID: <MPG.19738e916baa1cff9896a5_at_nntp.slnt.phub.net.cable.rogers.com>


Hello everyone.

I am a newbie to Oracle 8 so please be patient and thanks for your time. The problem is the above mentioned error. I have two databases successully created using SQL*Plus 8.03. They are as follows:

Create Table EMPLOYEES
(

    FName	VARCHAR(15)	NOT NULL,
    MINIT	CHAR, 
    LName	VARCHAR(15)	NOT NULL,
    SSN	CHAR(9)	NOT NULL,
    BDATE	CHAR(25),
    Address	VARCHAR(30),
    Sex	CHAR,
    Salary	DECIMAL(10,2),
    SUPERSSN	CHAR(9),
    DNO		INT,
  Primary Key(SSN),

  Foreign Key(SUPERSSN) REFERENCES EMPLOYEES(SSN),   Foreign Key(DNO) REFERENCES DEPARTMENT(DNUMBER) );

Create Table DEPARTMENT
(

   DNAME		VARCHAR(15)	NOT NULL,
   DNUMBER		INT	NOT NULL,
   MGRSSN		CHAR(9)	NOT NULL,
   MGRSTARTDATE		CHAR(25),

 Primary Key(DNUMBER),
 Unique(DNAME),
 Foreign Key(MGRSSN) References EMPLOYEES(SSN) );

I then try to insert a record into the Employees table and received the above mentioned error:

Insert into employees Values
 ('John', 'B', 'Smith', '123456789', '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, '33344555', 5);

I then decided to try to insert a record into the Department table and received the exact same error. I have read on google.com since the Employees table has a foreign key which references a primary key in the Department table I should make sure the Foreign key value exist in the Department table first. But I'm caught in a loop because both tables have foreign keys which point to the primary key in the other table.

Any help would be greatly appreciated.

Thanks again

Victor Received on Mon Jul 07 2003 - 21:07:30 CEST

Original text of this message