Problem with Foreign Keys

Developers Databases
#1

Using MySQL. Whan I create a Foreign Keys in a database between Parent/Child tables, I get into problems. Saving a form generates an arror. When I remove the key everything works fine. Obviously I want the database to take care of RI, so what alse could be done to have this working ?

Any ideas ?

Arthur

In this case: dps_vendors is PARENT table and dps_xproven i a child table, FK_Ven2Xpro = foeign key

Error inserting:
Cannot add or update a child row: a foreign key constraint fails (dpsi.dps_vendors, CONSTRAINT FK_Ven2Xpro FOREIGN KEY (VendorID) REFERENCES dps_xproven (VendorID) ON DELETE NO ACTION ON UPDATE NO ACTION)

#2

Re: Problem with Foreign Keys

hi. i am using a ton of FK in dozens of tables, so i know it can work. Are you able to save the data in your table using phpmyadmin or other tool. perhaps trying that could help debug what field is wrong. Those issues can be complex, but just try to break it down step by step. Post your tables and sample data, so we can help.

#3

Re: Problem with Foreign Keys

here is a view of relationship and FK for 2 tables (which is part of larger structure) (see atached images).
ProGroups (for ProductGroups) is a parent for Products. The relationship is set ONE(ProGroups) TO MANY(Products).

Whane Adding new record I get this errpr:

Error inserting:
Cannot add or update a child row: a foreign key constraint fails (dpsi.dps_progroups, CONSTRAINT FK_ProductGroupID2Products FOREIGN KEY (ProductGroupID) REFERENCES dps_products (ProductGroupID) ON DELETE NO ACTION ON UPDATE NO ACTION)

SC6_FKErrors.png

#4

Re: Problem with Foreign Keys

relationship in Workbench

SC6_FKErrors2.png

#5

Re: Problem with Foreign Keys

do you define your foreign keys and joins in MySQL (i.e. MySQL creation script) or in SC ?

Arthur