[Catalyst] user maintenance

[Jon Schutz]

On Mon, 2007-09-03 at 12:21 +0930, Toby Corkindale wrote:
> Adam Bartosik wrote:
> >> MySQL 4.x and 5.0 will accept the above SQL syntax, but will silently
> >> ignore it, and not actually perform any referential integrity checking,
> >> nor cascading. ie. It all looks like it's working fine, until it breaks
> >> horribly when you get into detailed testing. You have been warned.
> > 
> > Please, please RTFM before writing such things, eg:
> > http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html
> > It is better to know how stuff works - there are different formats of
> > tables (storages) in MySQL so you can use what is better for your
> > needs and defaults are not always the best. It just like flexibility
> > in Catalyst compared to ... Rails?
> 
> Attached are two SQL files. One of them uses "default" MySQL tables, the
> other one creates them as InnoDB tables.
> I have run them against MySQL 5 and in both cases, the foreign key
> constraints and cascading deletes are silently ignored. How is that
> "better for your needs"?
> You're welcome to verify this yourself. I include the captured output
> too, although it'll make more sense if you look at the SQL first.
> I also include the output from running the normal SQL through Postgres
> 8, and you can see the difference - the cascading delete occurs, and an
> error is thrown when an invalid foreign key is inserted.
> 

Here's a quote from the mysql ref manual,
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

"Important

The inline REFERENCES specifications where the references are defined as
part of the column spoecification are silently ignored by InnoDB. InnoDB
only accepts REFERENCES clauses when specified as part of a separate
FOREIGN KEY specification. "

That is, you've used a syntax which is silently ignored.  Your foreign
key specs should look something like this (cutting and pasting at random
one that DBIx::Class/SQL::Translator has generated from one of my
schema):

CONSTRAINT `service_fk_customer` FOREIGN KEY (`customer`) REFERENCES `customer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE,

-- 

Jon