I have 2 tables, roles and operations and are related in a third table operations_roles . This is my migration file:
$table = $this->table('roles');
$table ->addColumn('user_id','integer')->addForeignKey('user_id','users','id',array('delete'=>'CASCADE','update'=>'CASCADE'))
->addColumn('rolname','string')
->addColumn('roldescripcion','string')
->create();
$table = $this->table('operations');
$table ->addColumn('user_id','integer')->addForeignKey('user_id','users','id',array('delete'=>'CASCADE','update'=>'CASCADE'))
->addColumn('opename','string')
->addColumn('opedescripcion','string')
->create();
$table = $this->table('operations_roles');
$table ->addColumn('operation_id','integer')->addForeignKey('operation_id','operations','id',array('delete'=>'CASCADE','update'=>'CASCADE'))
->addColumn('role_id','integer')->addForeignKey('role_id','operations','id',array('delete'=>'CASCADE','update'=>'CASCADE'))
->create();
Everything else is created with [Bake] [1].
So, what happens when I'm going to create a role and assign it to operations already created is that at first it does it right but after the third creation of a role throws me this error:
Error: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails ('prueba1'.'operations_roles', CONSTRAINT 'operations_roles_ibfk_2' FOREIGN KEY ('role_id') REFERENCES 'operations' ('id') ON DELETE CASCADE ON UPDATE CASCADE)
And the truth is that I do not understand, if you keep 2 roles with their respective operations, why when I create another one does this error return?
EXAMPLE: first I create 2 operations Operation 1 and Operation 2. When I create the role Role 1 I assign operations 1 and 2. I create another role Role 2 with Operation 1 and Role 3 with Operation 2. When I go to edit or add another role with operations returns that error. Be careful if I create a role alone without operations, there is no problem. The problem is when it is saved in operations_roles