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

[Violenza]

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

[monte96]

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.

[Violenza]

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.

[Violenza]

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 =(