really need a VB/Oracle wiz for this one...

I have been tasked with taking the contents of a FileMaker Pro database (1 table) and convert it over
to a normalized 10 table Oracle 7.3 database using VB6 SP3.

Everything is going great with the exception of one problem. I will simplify the situation to hopefully
make it more readable / understandable.

I have two tables. In the main data table I have a field, 'employeeID' (not the primary key). The second
table is a look up table to relate employeeID's with the employee's name. The scripts I'm using to generate
the databases correctly have 'employeeID' in the main table foreign-keyed to the look up table (where employeeID
in the second table is correctly identified as the primary key).

I have pre-populated the look up table via a script in Oracle SQL *Plus. Anytime I try to put a value into
the main table's employeeID field I get the following error.

---------------------------------------------------------
Run-time Error '-2147217900 (80040e14)':

[Oracle][ODBC][Ora]ORA-02291 : integrity constraint
(OPES.EmployeeID_FK) violated - parent key not found
---------------------------------------------------------

Here are the script snipets generating the table

(creating the table)

create table opes_audit (
tracking_number varchar2(6) not null,
employeeID varchar2(10) null)
storage (initial 16384 next 16384 maxextents 500
pctincrease 0 freelists 5)
tablespace opes_data
parallel (degree 1)
nocache
/

(creating the look up table)

create table opes_employee (
employeeID varchar2(10) not null,
employee_fullName varchar2(30) null)
storage (initial 16384 next 16384 maxextents 500
pctincrease 0 freelists 5)
tablespace opes_data
parallel (degree 1)
nocache
/

(altering the main table to add the foreign key constraint to the main table)

alter table opes_audit add constraint
employeeID_fk foreign key
(employeeID)
references opes_employee
(employeeID)
/

(altering the lookup table to add the primary key constraint to the employeeID field)
alter table opes_employee add constraint
employeeID_pk primary key
(employeeID)
using index
storage (initial 16384 next 16384 maxextents 500
pctincrease 0 freelists 5)
tablespace opes_indx
/


I am working to a deadline so any thoughts are way appreciated.

Thanks

Step through your code in VB and put see what the value is that is being put into the employeeid field in the main table. Sounds like maybe your missing a record in your lookup table.

ive stepped through my code more times than you can imagine ;) The SQL string im sending to the ADO connection is correctly populated with data.

when using a SQL statement like the following in VB...

INSERT INTO <tablename> (<field1>, <field2> ) VALUES (<value1>, <value2>)

none of the <fieldx>'s can be a foreign key to another table.

If you take out the foriegn key and follow the above statement with something like this

UPDATE <tablename> SET <field1>=[']<value1>[']

it works fine if you enter the foriegned keyed value by itself. but if its part of a larger SQL statement it wont work. maybe this is a limitation of the Oracle ODBC driver im using? 8.00.59.00.

While I'm glad i found a fix around my problem, this doubled my number of transactions to the Oracle DB =(