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”
anoopsachari | May 12, 2010 | Comments 8
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 .
symfony doctrine:insert-sql
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 .
Filed Under: symfony
Oh, thank you very much mate, I had the same problem with Code Igniter and Doctrine…! What a headache!!
@Bruno did my post help you to solve the issue ?
I have same problem. like this:
Article:
tableName: article
columns:
id: { type: integer(4), primary: true, autoincrement: true }
title: { type: string(255), notnull: true, }
content: { type: clob }
author_id: { type: integer(4), notnull: true }
category_id: { type: integer(4) }
relations:
Author:
foreignAlias: Articles
onDelete: CASCADE
local: author_id
foreign: id
Category:
foreignType: one
local: category_id
foreign: id
Author:
tableName: author
columns:
name: { type: string(20) }
Category:
tableName: category
columns:
name: { type: string(255), notnull: true }
and it don’t work. I must change all table’s primary to int. some thing like :
Article:
tableName: article
columns:
id: { type: integer, primary: true, autoincrement: true }
title: { type: string(255), notnull: true, }
content: { type: clob }
author_id: { type: integer, notnull: true }
category_id: { type: integer }
relations:
Author:
foreignAlias: Articles
onDelete: CASCADE
local: author_id
foreign: id
Category:
foreignType: one
local: category_id
foreign: id
Author:
tableName: author
columns:
name: { type: string(20) }
Category:
tableName: category
columns:
name: { type: string(255), notnull: true }
and it’s work.
Thaks for share
Hi. Just wanted to say that you’ve got a database error in your title
thank you for saving my time!
Legend, solved my problem.. many thanks.
Thank a ton man… Your solution saved me… This problem drove me nuts and your solution worked like a charm.
Thanks
Thank you! You saved my day.