Hi, I'm developing my first symfony application and I used MySql
workbench and the doctrine export plugin to do my data model.
Now I'm finished modeling and if I let MySQL WB generate the DB there
is no problem. If I export to a yaml file I get this (only the
problematic small part of the model):
[CODE]
sfGuardUserProfile:
tableName: sf_guard_user_profile
columns:
id:
type: integer(4)
primary: true
notnull: true
autoincrement: true
user_id:
type: integer(4)
primary: true
notnull: true
email:
type: string(80)
unique: true
notnull: true
relations:
sfGuardUser:
local: user_id
foreign: id
foreignAlias: sfGuardUserProfiles
onDelete: cascade
onUpdate: restrict
options:
type: InnoDB
PhoneNumber:
tableName: phone_number
columns:
id:
type: integer(8)
primary: true
unsigned: true
notnull: true
autoincrement: true
profile_id:
type: integer(4)
primary: true
notnull: true
user_id:
type: integer(4)
primary: true
notnull: true
number:
type: string(20)
notnull: true
relations:
sfGuardUserProfile:
local: profile_id
foreign: id
foreignAlias: PhoneNumbers
onDelete: cascade
onUpdate: restrict
options:
type: InnoDB
[/CODE]
As you can see I created an identifying relationship between
sfGuardUser and sfGuardUserProfile. till here no problem.
then I created an identifying relation between sfGuardUserProfile and
PhoneNumber and here I get this error when running ./symfony
doctrine:build-all-reload --no-confirmation (I run version 1.2.9 with
doctrine)
[CODE]SQLSTATE[HY000]: General error: 1005 Can't create table './
myDBDEV/#sql-9ca_51.frm' (errno: 150). Failing Query: ALTER TABLE
sf_guard_user_profile ADD FOREIGN KEY (id) REFERENCES phone_number
(profile_id)[/CODE]
and mysql SHOW INNODB STATUS gives me:
[CODE]
------------------------
LATEST FOREIGN KEY ERROR
------------------------
091005 21:13:29 Error in foreign key constraint of table
fotolion_nsnbDEV/#sql-9ca_60:
FOREIGN KEY (id) REFERENCES phone_number(user_id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
[/CODE]
I get this error for each table that has an identifying relationship
to a table that has itself an identifying relationship (so a table
that has a 3 fields primary key).
if I make the relationship between sfGuardProfile and PhoneNumber non
identifying (remove the user_id and profile_id from the primary key)
like this:
[CODE]
PhoneNumber:
tableName: phone_number
columns:
id:
type: integer(8)
primary: true
unsigned: true
notnull: true
autoincrement: true
profile_id:
type: integer(4)
notnull: true
user_id:
type: integer(4)
notnull: true
number:
type: string(20)
notnull: true
relations:
sfGuardUserProfile:
local: profile_id
foreign: id
foreignAlias: PhoneNumbers
onDelete: cascade
onUpdate: restrict
options:
type: InnoDB
[/CODE]
then the build-all-reload command doesn't give any errors.
the relevant sql generated by doctrine is:
identifying rel:
[CODE]
CREATE TABLE phone_number (id BIGINT UNSIGNED AUTO_INCREMENT,
profile_id INT, user_id INT, number VARCHAR(20) NOT NULL, type VARCHAR
(255) NOT NULL, PRIMARY KEY(id, profile_id, user_id)) ENGINE = InnoDB;
CREATE TABLE sf_guard_user_profile (id INT AUTO_INCREMENT, user_id
INT, avatar BIGINT UNSIGNED NOT NULL, resumee BIGINT UNSIGNED NOT
NULL, email VARCHAR(80) NOT NULL UNIQUE, last_name VARCHAR(45) NOT
NULL, first_name VARCHAR(45) NOT NULL, birth_day DATE, gender VARCHAR
(255), activities VARCHAR(255), interests VARCHAR(255), music_taste
VARCHAR(255), creativity_field MEDIUMTEXT, short_summary VARCHAR(255),
books VARCHAR(255), films VARCHAR(255), validate VARCHAR(17), INDEX
resumee_idx (resumee), PRIMARY KEY(id, user_id)) ENGINE = InnoDB;
ALTER TABLE phone_number ADD FOREIGN KEY (user_id) REFERENCES
sf_guard_user_profile(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE sf_guard_user_profile ADD FOREIGN KEY (user_id) REFERENCES
sf_guard_user(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE sf_guard_user_profile ADD FOREIGN KEY (id) REFERENCES
phone_number(user_id);
[/CODE]
NON identifying rel:
[CODE]
CREATE TABLE phone_number (id BIGINT UNSIGNED AUTO_INCREMENT,
profile_id INT NOT NULL, user_id INT NOT NULL, number VARCHAR(20) NOT
NULL, type VARCHAR(255) NOT NULL, INDEX profile_id_idx (profile_id),
PRIMARY KEY(id)) ENGINE = InnoDB;
CREATE TABLE sf_guard_user_profile (id INT AUTO_INCREMENT, user_id
INT, avatar BIGINT UNSIGNED NOT NULL, resumee BIGINT UNSIGNED NOT
NULL, email VARCHAR(80) NOT NULL UNIQUE, last_name VARCHAR(45) NOT
NULL, first_name VARCHAR(45) NOT NULL, birth_day DATE, gender VARCHAR
(255), activities VARCHAR(255), interests VARCHAR(255), music_taste
VARCHAR(255), creativity_field MEDIUMTEXT, short_summary VARCHAR(255),
books VARCHAR(255), films VARCHAR(255), validate VARCHAR(17), INDEX
resumee_idx (resumee), PRIMARY KEY(id, user_id)) ENGINE = InnoDB;
ALTER TABLE phone_number ADD FOREIGN KEY (profile_id) REFERENCES
sf_guard_user_profile(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE sf_guard_user_profile ADD FOREIGN KEY (user_id) REFERENCES
sf_guard_user(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE sf_guard_user_profile ADD FOREIGN KEY (id) REFERENCES
phone_number(profile_id);
[/CODE]
Any ideas on why this happens? I've more a db background than oop so I
like to have a strong db model. Is it may be better using symfony/
doctrine to avoid such complex keys and just use surrogate ids with
weak (non identifying) relations?
thanks a lot
Marco
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to
symfony-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
-~----------~----~----~----~------~----~------~--~---
