Hi Jon, you're right, looks like my suggestion doesn't work for foreign key constraint. I was thinking of CHECK constraint. I guess you'll have to use a trigger as Paul suggested, or perhaps inserting a dummy record in the master table to satisfy the foreign key requirement. Basically, i'd suggest that you fix up your data in IB (before enabling the constraint), it'll be much faster.

On Wed, Jun 20, 2001 at 04:39:38AM -0000, jvanderreest@... wrote:
>
> 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"