Symfony / sfGuardUser: MySQL errno: 150 Failing Query "ALTER TABLE sf_guard_user......."

I've been trying to link up my symfony project (using Doctrine) to sfGuardPlugin with profiles. However, when trying to rebuild the schema and insert sql, I encountered the following error:

SQLSTATE[HY000]: General error: 1005 Can't create table 'phoenix.#sql-740_d7' (errno: 150). Failing Query: "ALTER TABLE sf_guard_user_profile ADD CONSTRAINT sf_guard_user_profile_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id)". Failing Query: ALTER TABLE sf_guard_user_profile ADD CONSTRAINT sf_guard_user_profile_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id)


This isn't an incredibly useful error message. Turns out that it's to do with the way MySQL handles cascading constraints. To cut a long story short, there's more info in the links posted below, but basically you need to remember that "type: integer" does not mean it will be stored as INTEGER in MySQL. As a matter of fact, "type: integer" in a schema.yml file translates into a "BIGINT(20)" in MySQL - which means when you add a constraint to a normal INTEGER field, it fails.

To store an integer value in schema.yml, use "type: integer(4)". This will translate into a type of INTEGER in MySQL.

Explained in a comment I found:

Something you might be interested in, that took me quarter an hour. integer(11) in schema.yml does not result in int(11) in MySQL, but instead you have to write integer(4) in your schema.yml. integer integer int/serial integer(1) tinyint smallint/serial integer(2) smallint smallint/serial integer(3) mediumint int/serial integer(4) int int/serial integer(5) bigint bigint/bigserial However, in my case integer without a number resulted in BIGINT.

More info:
http://trac.symfony-project.org/wiki/sfGuardPluginExtraDocumentation
http://mirmodynamics.com/post/2009/04/02/SQLSTATE%5BHY000%5D%3A-General-error%3A-1005-Can-t-create-table-*-%28errno%3A-150%29 (especially see the last comment)
http://bytes.com/topic/mysql/answers/865699-cant-create-table-errno-150-foreign-key-constraints