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.