Symfony + Doctrine - possible to link more than 2 tables/models together?

Question

I've created an sfGuardUserProfile model that has a relation to the sfGuardUser model. Then I define another model with a relation to sfGuardUserProfile. I don't get any errors when I create the database, but when I try saving data to sfGuardUserProfile in my actions file, I get this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

In my schema.yml, I am defining the relationships as one to one.

I'm not sure why this would be failing. Does Doctrine simply not support adding a new relationship to a model that already has a relationship?

Edit Here's my schema.yml:

sfGuardUserProfile:
  tableName: sf_guard_user_profile
  columns:
    sf_guard_user_id: { type: integer(4) }
    email:            { type: string(255) }
  relations:
    User:
      class:        sfGuardUser
      type:         one
      foreignType:  one
      onDelete:     CASCADE
      local:        sf_guard_user_id
      foreign:      id
      foreignAlias: Profile

FacebookAccount:
  tableName: facebook_account
  columns:
    user_id: { type: integer(4) }
    page_id: { type: integer }
  relations:
    sfGuardUserProfile:
      type:         one
      foreignType:  one
      class:        sfGuardUserProfile
      local:        user_id
      foreign:      sf_guard_user_id
      onDelete:     CASCADE
      foreignAlias: FacebookAccount

I encounter the error when I do this:

$profile = $this->getUser()->getProfile();
$profile->setEmail('[email protected]');
$profile->save();

The generated SQL:

INSERT INTO sf_guard_user_profile (sf_guard_user_id, email) VALUES (?, ?) - (1, [email protected])

The exact error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`site`.`sf_guard_user_profile`, CONSTRAINT `sf_guard_user_profile_sf_guard_user_id_facebook_account_user_id` FOREIGN KEY (`sf_guard_user_id`) REFERENCES `facebook_account` (`user_id`))

Answer 1

You have to ensure that you don't break database integrity: http://msdn.microsoft.com/en-us/library/ms175464.aspx . You can reach that inserting rows in correct order, for example:

$sfGuardUser = new sfGuardUser();
$sfGuardUser->id = 1;
$sfGuardUser->save();

$sfGuardUserProfile = new sfGuardUserProfile();
$sfGuardUserProfile->user_id = $sfGuardUser->id;
$sfGuardUserProfile->save();

or like this:

$sfGuardUser = new sfGuardUser();
$sfGuardUser->id = 1;

$sfGuardUserProfile = new sfGuardUserProfile();
$sfGuardUserProfile->User = $sfGuardUser;
sfGuardUserProfile->save();

Answer 2

I think your problem is your FacebookAccount model not linking to the primary key on your Profile model and Doctrine not knowing how to work with it. Either change your FacebookAccount to reference the Profile primary key:

  relations:
    sfGuardUserProfile:
      type:         one
      foreignType:  one
      class:        sfGuardUserProfile
      local:        user_id
      foreign:      id
      onDelete:     CASCADE
      foreignAlias: FacebookAccount

or relate to the primary key of sfGuardUser:

  relations:
    sfGuardUser:
      type:         one
      foreignType:  one
      class:        sfGuardUser
      local:        user_id
      foreign:      id
      onDelete:     CASCADE
      foreignAlias: FacebookAccount