Make MySQL's foreign keys to work
deelan
current Django creation scripts doesn't seem to be able to create FKs
on Mysql. Mysql requires that for each FK created you have to
explicitly setup an index. That's how i specify FK in some real-world
working schema of mine:
-- localized text for catalogue product
CREATE TABLE `ecomm_product_text` (
`lang` varchar(6) NOT NULL default '',
-- FK
`product_id` int(4) unsigned NOT NULL,
INDEX product_idx (product_id),
FOREIGN KEY (product_id) REFERENCES ecomm_product(id) ON DELETE
CASCADE,
`title` text NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`lang`,`product_id`)
) TYPE=InnoDB CHARSET='UTF8';
in the above table ecomm_product_text references to an external table
product via a product_id field.
after a brief look at the django sources it seems to me that
django.core.management modules takes care of the creations of such
scripts.
as usual mysql docs are a "joy" to read:
''For table types other than InnoDB, it is possible when defining a
column to use a REFERENCES tbl_name(col_name) clause, which *has no
actual effect*, and serves only as a memo or comment to you that the
column which you are currently defining is intended to refer to a
column in another table.''
-- from http://dev.mysql.com/doc/mysql/en/example-foreign-keys.html
understand? REFERENCES is just a memo. :) but i'm not gonna push any
"memos" here, i would like to have some form of referential integrity
here, just like the postgres folks have.
so let's dig further. ahhh, look at this instead:
''
The syntax of a foreign key constraint definition in InnoDB looks like
this:
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
(...)
*Remember* to create the required indexes first.''
-- from
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html
but i bet that using a FOREIGN KEY command on a MyISAM table will
produce an error -- i hope i'm wrong.
Apart of that, current syntax to create models on the mysql backend
doesn't create any *real* FKs. Django management.py module could be
patched accordingly , but maybe it's better to start thinking of a
mysql-innodb backend that also will work with transactions.
thoughts?
-- deelan.