Re: Setting up foreign keys to match MS Access relationships

[Jonathan Shaltz]

Good afternoon, Mr. Muspratt-Rouse.
The foreign key constraint goes on the child table, in the form FOREIGN KEY (KeyFieldThisTable) REFERENCES ParentTable (KeyFieldParentTable) ON DELETE CASCADE.
Three possibilities come to mind. Are you using InnoDB tables? MySQL only supports foreign keys on InnoDB. Are there parents for all of your data? MySQL won't let you create a constraint that would be violated by the existing data. Are the keys indexed in both tables? To be used in a foreign key constraint, a field needs to be indexed on its own, or it needs to be the first part of a multi-part index. MySQL will automatically create an index on the child's field, but you need to have an existing index in the parent table.
Hope that helps!