0

I am trying to get this SQL schema together for an Employee Database with four different tables (Employee information, Work information, company information, and manager information) and got the following error when attempting building the schema:

Schema Creation Failed: ORA-02291: integrity constraint (USER_4_07688.SYS_C00777054) violated - parent key not found

Here is my schema code:

create table Employee(
    Lastname    varchar(10),
    FirstName   varchar(10),
    MidInitial  char(1),
    gender      char(1),
    street      varchar(10),
    city        varchar(10),
    primary key(Lastname, FirstName, MidInitial));

create table company(
    company_name    varchar(20),
    city    varchar(10),
    primary key(company_name));

create table Works(
    Lastname    varchar(10),
    FirstName   varchar(10),
    MidInitial  char(1),
    company_name    varchar(20),
    salary      numeric(8,2),
    primary key(Lastname, FirstName, MidInitial, company_name),
    foreign key(Lastname, FirstName, MidInitial) references Employee,
    foreign key(company_name) references company);

create table Manages(
    Lastname    varchar(10),
    FirstName   varchar(10),
    MidInitial  char(1),
    ManagerLastname varchar(10),
    MFirstName  varchar(10),
    MMidInitial char(1),
    start_date  date,
    primary key(Lastname, FirstName, MidInitial, ManagerLastname, MFirstName, MMidInitial),
    foreign key(Lastname, FirstName, MidInitial) references Employee);

I've been going over and over this but I can't seem to find where the integrity constraint error would be (I thought that all the foreign keys and whatnot lined up correctly)...

Could someone please let me know if they see any blatant errors in my code that could be causing this error?

Thanks so much!

---UPDATE---

I just tested out each foreign key with the rest of the schema individually and located the offending statement:

foreign key(company_name) references company);

The table this foreign key is referencing has already been created, so is there something else I'm missing? Thanks again!

user1462294
  • 167
  • 4
  • 13
  • No, I don't. The above tables are all I should need, right? My schema was able to be built successfully until I added in the foreign key stuff so I'm not sure where that went wrong... – user1462294 Sep 18 '13 at 05:40
  • Well in this sqlfiddle http://sqlfiddle.com/#!4/1bf8c5 your schema compiles just fine. – peterm Sep 18 '13 at 05:46
  • just a hint: when you creating keys (primary or foreign) create named keys. That way it will be much easier to see which key the Oracle complaining about. As for the error above - I believe it is due to your primary key columns are not specified as `NOT NULL` – cha Sep 18 '13 at 05:48
  • Thanks so much for the help! I just realized the problem was in my code to insert tuples into the table; I had inserted company_name information into the Works table before inserting the company_name information into the company table so I think that's why the referential integrity issue popped up. – user1462294 Sep 18 '13 at 05:51

0 Answers0