FuelPHP Forums

MFCDeLorme

How can I tell Fuel allow my database to handle cascaded deletions? I have a user table, and I have modules adding new tables with one-to-one relationships to the user table. I want the database to enforce integrity, I don't want that responsibility to be Fuel's. I setup has_one relationships in the user object and belongs_to relationships in the module models. These work for traversing data between the objects without any problems. The Database is using InnoDB engine with Cascade Delete turned on, which works how I want it. If I delete a user using raw SQL it deletes records from the two related tables without any errors.
When I delete a user using Fuel ORM, the operation succeeds in the database, but I receive the following error:

Fuel\Core\FuelException [ Error ]: Primary key cannot be changed.

PKGPATH/orm/classes/model.php @ line 892

887            throw new FrozenObject('No changes allowed.');
888        }
889
890        if (in_array($property, static::primary_key()) and $this->{$property} !== null)
891        {
892            throw new \FuelException('Primary key cannot be changed.');
893        }
894        if (array_key_exists($property, static::properties()))
895        {
896            $this->_data[$property] = $value;
897        }
Backtrace

PKGPATH/orm/classes/model.php @ line 782
PKGPATH/orm/classes/hasone.php @ line 216
PKGPATH/orm/classes/model.php @ line 1152
APPPATH/classes/controller/users.php @ line 140
COREPATH/classes/controller/rest.php @ line 117
COREPATH/classes/controller/hybrid.php @ line 94
COREPATH/classes/request.php @ line 406
DOCROOT/index.php @ line 38

I found a post around 7 months ago regarding the same error, but only one response and not enough information to go by for a solution:
http://fuelphp.com/forums/topics/view/6803 From the original response I take it Fuel doesn't like it when we use an ID as a FK in relationships? Even so, it makes no sense to create a second FK field with duplicate contents when the data is one-to-one.
Does anyone know whether there is a setting that will tell Fuel's ORM to allow the DB to handle the delete and not throw an error? As for the model relationships:

USER
 protected static $_has_one = array(
  'profile' => array(
         'key_from' => 'id',
         'model_to' => 'Model_Profile',
         'key_to' => 'id',
         'cascade_save' => true,
         'cascade_delete' => false,
  ),
  'signoutsheet' => array(
         'key_from' => 'id',
         'model_to' => 'Model_Signoutsheet',
         'key_to' => 'id',
         'cascade_save' => true,
         'cascade_delete' => false,
  ),
 );

PROFILE
 protected static $_belongs_to = array(
  'user' => array(
   'key_from' => 'id',
   'model_to' => 'Model_User',
   'key_to' => 'id',
   'cascade_save' => true,
   'cascade_delete' => false,
  ),
 );

SIGNOUTSHEET
 protected static $_belongs_to = array(
  'user' => array(
   'key_from' => 'id',
   'model_to' => 'Model_User',
   'key_to' => 'id',
   'cascade_save' => true,
   'cascade_delete' => false,
  ),
 );

DELETE USER:
  $user = Model_User::find($id);
  $user->delete();

Harro

That is correct from an ORM point of view. From a database point of view, it's a child table with an FK pointing to the parent table. Which is the same implementation as for a one-to-many. It is the ORM that will enfore the 'one' in a has_one relation.

MFCDeLorme

I tried has_one on both sides with no luck, same error message as before. I then proceeded to try every combination of cascade_save and "on update" settings I could find. After more reading, the ORM creator pointed out that this is a limitation-by-design that Fuel's ORM disallows a foreign key to be used as a primary key in other tables. The justification provided was that the Primary Key should be "unique and unchanging". http://fuelphp.com/forums/topics/view/5924
http://docs.fuelphp.com/packages/orm/creating_models.html I fail to see the logic behind this, as the foreign key in a one-to-one relationship is pointing to another tables primary key, and that value will be both unique and unchanging. The error occurs when I want to delete orphaned records, not change the primary key. I am of the opinion that the DBMS should be responsible for maintaining data integrity, not the ORM, so I disagree with the decision to omit this as an option personally. In any event, "fixed" the problem by creating the redundant user_id column and modifying all of the related operational code to address the Fuel limitation.

Harro

You are entitled to your opinion, and in this case I tend to agree with you. However, we are a minority, and there are lots of RDBMS's out there that don't even support any means of enforing referential integrity. The issue with the primary key is that the ORM will break the relation before deleting the child. In case of this type of relation, it will set your PK to NULL, which will probably be a violation of your constraints, and it will erase the primary key so the delete will no longer work. If you implement it in such a way that you just start deleting stuff and assume the RDMBS will make sure everthing stays integer, you make the ORM useless for everyone else. It's a decision that has to be made, and afaik most ORM's have dealt with this dilemma in a similar fashion.

MFCDeLorme

Harro Verton, I want to thank you for your prompt and valuable replies. I was not trying to be obstinate, and after thinking over your last response I can see where the alternative would debilitate the ORM in other situations. I was aware of DB engines that don't handle referential integrity, like MyISAM, but I was under the impression that the R in RDBMS was an indicator that they "can". With regards to the Primary key error, originally I thought the data could not be orphaned since it was being handled by the DBMS, and assumed the normal lazy-loaded behavior would ignore related objects that had not already been loaded. When that failed I thought that maybe the cascade_save setting was trying to update the related models, but changing that also did not resolve the error. What I did not account for in my scenario was a larger scaled operation where the delete is not the only transaction. If the DBMS performs a cascaded delete, how would the ORM know of or handle potentially orphaned instances of the Model already loaded in the system? There isn't a clear cut answer I can derive from this situation; I am still of the opinion that the DBMS should be responsible for integrity enforcement, but I now totally understand where and why the application may disagree with this philosophy.

Harro

An ORM is a very complex beast. And our ORM is in functionality very simple compared to some others out there, which are more mature and often have an entire development team behind them. We can never compete with that, which is one of the reasons we're not going to continue development of our ORM. Then you have the difference between implementations based on the Active Record pattern and the Datamapper pattern. In the last one (which is the basis of our ORM), the ORM maintains persistence, which basically means it cached loaded objects. Which adds complexity if the RDBMS starts deleting records while the ORM is not aware of it, leaving the object cache in an invalid state. You can have the same problem with AR implementations, but usually those objects are short-lived so there is less of an issue. So in general having DB intelligence in both your DB abstraction layer and your RDBMS is a bad idea. Either use one or the other, or be very careful and know exactly what you are doing, document it well, and make sure other developers after you understand the consequences of your choice.

Dregond Rahl

Well to be honest its much better practice to be using a separate FK for each table. You can try if that fixes the problem. If it still causes the problem it might be because you are deleting it from the MySQL relationship before the ORM can find and delete the related item

MFCDeLorme

I am not dismissing this as a possible solution, but I am more curious as to whether Fuel can work without forcing me to create redundant columns. Given that it has one-to-one relationship support I would imagine there is a "better" solution. AFAIK a Unique Index is more constrained than a PK in that it prevents duplicate data in one column per rows in a table, whereas the PK identifies each row as a unique entity. In Our case, User ID is a Unique Index, why would it be better practice to use a redundant PK instead?

Harro

You have not defined a one-to-one relation here (in the way you define it), since it's a "has_one" at one end, and a "belongs_to" at the other. A belongs_to always expects an FK to the parent, as you should be able to decouple the child and connect it to another parent. I'm not sure if it will work if you define both sides of the relation as has_one, I don't think this scenario has been considered when designing the possible relations...

MFCDeLorme

I will give this a try tomorrow, but... http://docs.fuelphp.com/packages/orm/relations/has_one.html
'Specifies a one-to-one relationship to another model. The target model must include a "Belongs To" reference to the current model to allow the inverse relationship.' Documentation says it is one-to-one. It also explicitly states _has_one maps to _belongs_to, and _belongs_to can map to either _has_one or _has_many. Also, full syntax lets me specify which fields are what to eachother. However, you may be correct in that it expects the FK not to be a PK (or it does not have a contingency for that situation). I was hoping someone would know if that were the case, would save me a week of reading the backend, not that learning isn't beneficial but time is scarce. I suppose I am looking for a one-to-zero-or-one relationship. I understand what you mean about the parent/child situation, but I wouldn't decouple a profile and hand it to another User.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion