I have 2 tables, say 'Supplier' and 'Customer'

Supplier has a primary key, PK_Supplier based on the fields BusinessCode and SupplierCode (both NOT NULL)
Supplier contains several rows having BusinessCode = '001' and different valid SupplierCodes

Customer has BusinessCode (NOT NULL) and SupplierCode (NULLable) fields. It contains several rows having BusinessCode = '001', but all have NULL SupplierCodes.

When I try to apply a foreign key constraint to the Customer table, FK_Supplier also based on the fields BusinessCode and SupplierCode, by :

ALTER TABLE "Customer"
ADD CONSTRAINT "FK_Supplier" FOREIGN KEY ("BusinessCode", "SupplierCode")
REFERENCES "Supplier" ("BusinessCode", "SupplierCode")
ON UPDATE NO ACTION
ON DELETE NO ACTION

I get :

ElevateDB Error #1004 An error occurred with the statement at line 3432 and column 19 (The foreign key constraint FK_Supplier for the table Customer has been violated (The foreign key 001, does not match any existing primary or unique key in the table Supplier))

which i suppose is reasonable. (Or is it ?)

However if I save the Customer data to CSV and delete all entries from Customer, then apply the FK constraint as above, I can now add back the deleted rows to Customer from CSV without error. This seems wrong, Anybody have any idea what's going on ?

thanks in advance
Tony Wood

Sorry fellas - please ignore this thread.

I scratched my head over this problem for ages before realising I had one row in the Customer table which had '' (i.e. the empty string) as the SupplierCode instead of NULL. It was hiding down the bottom, out of sight amongst all the NULL values. Once i replaced '' with NULL for that row the constraint problem naturally goes away and all is well with the rhythm of the universe again.

<sigh>