MySQL 4.1 (and earlier, I think) supports foreign keys if your table storage is "InnoDB", instead of the default "myISAM". This is also how you get proper commit() and rollback() behavior in MySQL. http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html
To make an InnoDB table, the CREATE statement looks like this: CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; You can also do an ALTER TABLE to convert a myISAM table to InnoDB, but then you would also have to go in and add the foreign key constraints via ALTER TABLE. -- Wade Michael Bayer wrote: > OK, this is terrific - but here is the zillion dollar question: how do > I get the FOREIGN KEY information back from a DESCRIBE (or similar) ? > where are you seeing that it "stores" the foreign key in one case and > not in the other (AFAIK mysql 4.1 doesnt actually *support* foreign > keys ?) > > On Feb 5, 2006, at 8:52 AM, Alastair Houghton wrote: > >> Hi all, >> >> I'm currently evaluating SQLAlchemy for a fair-sized project; I like >> the way it doesn't force data objects to inherit from something from >> its own framework, and I also like the way that all the table >> definitions and database mappings can be put in one place rather than >> distributing them throughout the code. >> >> Anyway, I noticed that MySQL (which I'm using as a back end) wasn't >> storing the foreign key constraints, although there seemed to be some >> attempt in the code to mark them; it looks to me like the version of >> MySQL I'm using (4.1.13) doesn't support---or rather, *ignores*---the >> syntax that mysql.py is generating for foreign key references. This >> patch >> >> Index: lib/sqlalchemy/databases/mysql.py >> =================================================================== >> --- lib/sqlalchemy/databases/mysql.py (revision 905) >> +++ lib/sqlalchemy/databases/mysql.py (working copy) >> @@ -215,6 +215,6 @@ >> if first_pk and isinstance(column.type, types.Integer): >> colspec += " AUTO_INCREMENT" >> if column.foreign_key: >> - colspec += " REFERENCES %s(%s)" % >> (column.column.foreign_key.column.table.name, >> column.column.foreign_key.column.name) >> + colspec += ", FOREIGN KEY (%s) REFERENCES %s(%s)" % >> (column.name, column.column.foreign_key.column.table.name, >> column.column.foreign_key.column.name) >> return colspec >> >> >> changes the code so that it adds a separate foreign key constraint >> instead, which does seem to work. >> >> Kind regards, >> >> Alastair. >> >> -- >> http://www.alastairs-place.net >> ------------------------------------------------------- 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
