Now for InnoDB support on the table.create() side.
For MySQL version >= 4.1.2, the db engine will automatically create the
appropriate foreign key index, so stock Sqlalchemy table.create()
behavior should work just fine -- provided you have already set the
default table type to InnoDB, either in my.cnf or by saying
engine.execute("SET @@table_type = 'InnoDB'", {}).
(My own MySQL setup is 4.0.something, so I can't actually test this.)
For MySql 3.23.44 <= version < 4.1.2, the CREATE TABLE statement must
explicitly name the foreign key indexes to be created, so as Sqlalchemy
now stands the table.create() method will not work with an InnoDB table
that has foreign keys.
My own approach right now is, during development I create tables as
MyISAM (which simply ignores FOREIGN KEY clauses in CREATE TABLE
statements). Then I convert each table to InnoDB:
engine.execute("ALTER TABLE %s ENGINE=INNOdb" % table.name, {})
This gives me proper commit/rollback behavior, though the foreign key
constraints do not actually exist on the tables.
To create tables for production, I'll do a SHOW TABLE CREATE, manually
edit the statement to make my foreign key constraints, and run it.
-- Wade
MySQL Reference:
http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html
"""
Starting from MySQL 3.23.44, InnoDB features foreign key constraints.
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}]
Foreign keys definitions are subject to the following conditions:
*
Both tables must be InnoDB tables and they must not be TEMPORARY
tables.
*
In the referencing table, there must be an index where the foreign
key columns are listed as the first columns in the same order. Starting
with MySQL 4.1.2, such an index is created on the referencing table
automatically if it does not exist.
*
In the referenced table, there must be an index where the
referenced columns are listed as the first columns in the same order.
*
Index prefixes on foreign key columns are not supported. One
consequence of this is that BLOB and TEXT columns cannot be included in
a foreign key, because indexes on those columns must always include a
prefix length.
*
If the CONSTRAINT symbol clause is given, the symbol value must be
unique in the database. If the clause is not given, InnoDB creates the
name automatically.
"""
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
