Sindu wrote:

> Just create the table first in IB/FB without the constraints (check,
> foreign key, primary key), etc, and then transfer your FoxPro data
> to this table, and after that, create the table constraint. The only
> caveat is, you won't be able to edit the data that violates the
> constraint.

Sindu,
I think that I've done as you suggested but it still doesn't seem to
work. I've created a test case and supplied the SQL below along with
the error message.


Paul wrote:

> I would say that part of the migration process will have to involve
> a clean up of the data if you want to use the built-in integrity
> constraints. Sindunata's explanation misses one thing - during the
> data import use 'before insert' triggers to tidy up the rogue or
> missing values.

Paul,
I totally agree that the data needs to be cleaned up, but if possible
I would like to leave that to after the migration process. Thanks for
the 'before insert' trigger idea to clean things up. Would it be
possible to use a trigger to trap offending records and have them
inserted into an 'Orphan table' instead of the 'Real' table. If so,
How would I go about that?


Ann wrote:

> I'd suggest using triggers rather than foreign key constraints.
> Define the triggers after you have the data loaded.

Ann,
I think I will follow your advice and use triggers instead of
constraints, especially since I noticed a thread on the IBObjects News
Group (Subject: Re: Foreign Key) discussing the use/abuse of 'Foreign
Keys'.


SQL EXAMPLE SCRIPT
==================

set sql dialect 3;

connect 'omdev3:C:\Program Files\Borland\InterBase\Databases\Jvr.gdb'
user 'SYSDBA';

/* first, create the 'Detail' table */
create table CUSTOMER(
CUSTID integer not null,
SURNAME char(40),
FIRSTNAME char(40),
CLASS integer default 0 not null,
constraint PRIMARY_CUSTOMER primary key (CUSTID)
);

/* and insert some 'incorrect' data which violates the constraint */
insert into CUSTOMER(CUSTID, SURNAME, FIRSTNAME, CLASS)
values (1, 'Vander Rest', 'John', 29);
insert into CUSTOMER(CUSTID, SURNAME, FIRSTNAME, CLASS)
values (2, 'Kay', 'Greg', 43);


/* then create the 'master' table */
create table CUSTOMER_CLASS(
CLASSID integer not null,
DESCRIPTION char(40),
constraint PRIMARY_CUSTOMER_CLASS primary key (CLASSID)
);

/* and insert some data. */
insert into CUSTOMER_CLASS(CLASSID, DESCRIPTION)
values (1, 'Active');
insert into CUSTOMER_CLASS(CLASSID, DESCRIPTION)
values (2, 'Inactive');
insert into CUSTOMER_CLASS(CLASSID, DESCRIPTION)
values (3, 'Deleted');

/* now apply the constraint. */
/* We don't want this to check the existing data, yet it seems to. */
alter table CUSTOMER
add constraint FOREIGNKEY_CLASS
foreign key (CLASS)
references CUSTOMER_CLASS (CLASSID);

ERROR MESSAGE:
--------------
violation of FOREIGN KEY constraint "PRIMARY_CUSTOMER_CLASS" on table
"CUSTOMER_CLASS"



How do we stop this from happening???

Thanks all and Regards

John Vander Reest