RSSAll Entries Tagged With: "foreign key"

Symfony | Doctrine | Error | SQLSTATE[HY000]: General error: 1005 Can’t create table XXX.#sql-818 _1′ (errno: 121). Failing Query:” ALTER TABLE xxx ADD CONSTRAINT …. FOREIGN KEY (xyz) REFERENCES XXX(id) ON DELETE CASCADE”

Symfony is a web application framework written in PHP which follows the model-view-controller (MVC) paradigm . A symfony developer may use Object-Relational Mapping (ORM) – Propel or Doctrine . The earlier version of symfony supported propel and i was using it . But in updated versions symfony supported Doctrine and was forced to update my skills in ORM .

Configuring symfony is pretty simple and model and sql was generated successfully . But on executing the command line argument to insert sql .

[php]

symfony doctrine:insert-sql

[/php]

i got an error :

SQLSTATE[HY000]: General error: 1005 Can’t create table XXX.#sql-818_1′ (errno: 121). Failing Query:” ALTER TABLE xxx ADD CONSTRAINT …. FOREIGN KEY (XYZ) REFERENCES XXX(id) ON DELETE CASCADE”

The error got on my nerves and couldn’t figure out what was it all about . But i was sure its something related to foreign key and after some research i got a fix for it and i am sharing it with you .

There is an issue with your ID columns. Normally this has to do with the foreign key and primary keys having different length constraints. Your fix:

Make sure that the length constraints match. If you aren’t specifying a length, you can simply remove the ID field from your schema.yml, and let Doctrine create it for you, or if you want to be explicit, you can set a length and make sure it is matched in the related foreign key field.

MetroUsers:
actAs: { Timestampable: ~ }
columns:
username: { type: string(255), notnull: true, unique: true }
password: { type: string(255), notnull: true }
hash: { type: string(255), notnull: true }

MetroUserDetails:
actAs: { Timestampable: ~ }
columns:
metro_user_id: { type: integer, notnull: true }
ref_from: { type: string(255), notnull: true, unique: true }
mobilenumber: { type: string(255), notnull: true }
is_mobile_valid: { type: boolean, notnull: true, default: 0 }
email_id: { type: string(255), notnull: true }
relations:
MetroUsers: { onDelete: CASCADE, local: metro_user_id, foreign: id, foreignAlias: MetroUserDetail }

Here id is generated automatically for MetroUsers and make sure that metro_user_id is of type integer .