Axel Schwenke <axel.schwenke@gmx.de> wrote:
>
> BTW, I just read "Note that InnoDB's FOREIGN KEY system tables
> store constraint names as case-insensitive, with the MySQL
> standard latin1_swedish_ci collation."
>
> So "Frühling" = "fryling" for InnoDB :-)

<grr>

"Frühling" = "fryhling"


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Thanks Axel,

Axel Schwenke wrote:
> "Ted" <r.ted.byers@rogers.com> wrote:
> >> [snip]
>
> If your question was "how to transport schema changes from development
> to production in a trackable way - without overwriting production from
> scratch every time" - I don't have a good answer yet.
>
I'd love to hear about it if you find a good solution. I was told
today that my scripts for creating the tables and various indeces are
fine during development leading up to testing and finally deployment,
but that we need to be able to alter them from development through to
production in a trackable way and without risking the data in the
production deployment. Several of these tables will be used to manage
data feeds, some of which amount to close to ten megabytes a day.
After a while, I fear changes, especially in indexing, may become
outrageously expensive.


BTW, I did not know about "SHOW ENGINE INNODB STATUS;" Kai's
suggestion proved to be the most important thing I learned here since
it proved very valuable in learning what MySQL was complaining about.
I found three kinds of errors leadng to inconsistent behaviour in
syntactically correct foreign key constraint definitions.

1) I hadn't realized the foreign ley name had to be unique across the
database. Providing names I could guarantee would be unique, fixed
many of my problems.

2) In some cases, I'd forgotten to create indeces on the fields
involved in the constraint. Fixing this solved a few more of my
problems.

3) In some cases, involving tables I hadn't looked at in a few weeks,
I'd made the mistake of having different types for the fields that were
to be related in the foreign key constraint. Fixing this solved the
rest.

Thanks one and all for your assistance in this matter.

Ted