OK, thanks very much :o)

--- In firebird-support@yahoogroups.com, Lucas Franzen <luc@r...>
wrote:

>
>
> yaedos2000 schrieb:
> >
> > Hi,
> >
> > I'm trying to convert an already existing field into a foreign

key,

> > which references the primary key of another table, but get the
> > following error message:
> >
> > ---------- STATEMENT ----------
> >
> > alter table TBL_HACS
> > add constraint FK_TBL_HACS_3
> > foreign key (VATCODE)
> > references TBL_C_VAT_RATES(VATCODE)
> >
> >
> > ---------- ERROR MESSAGE ----------
> >
> > violation of FOREIGN KEY constraint "".
> > violation of FOREIGN KEY constraint "INTEG_296" on
> > table "TBL_C_VAT_RATES".
> >
> > The primary key is being used as a foreign key by other tables
> > without any table.
> >
> > The table of the field I'm trying to convert already contains

data -

> > is this what is causing the problem? If so, is there a way to

make it

> > a foreign without deleting the existing data?
>
> Yes, it seems so.
>
> You can find out the missing records by query like:
>
> SELECT VATCODE FROM TBL_HACS T
> WHERE NOT EXISTS (
> SELECT 1 FROM TBL_C_VAT_RATES R
> WHERE T.VATCODE = R.VATCODE
> )
>
> If you have other records which will contain invalid references the

only

> way to make it work is:
>
> a) either set these values of TBLS_HACS.VATCODE to null
> (if the column is defined nullable)
>
> b) insert appropropiate master-record(s) into TBL_C_VAT_RATES

before.

>
>
> Luc.