When deleting hasOne or hasMany associations, should the foreignKey be set to NULL?

Question

Given :

Group hasMany Persons

but the relationship is independent (ie. Persons can exist without belonging to a Group), should the foreign key in the persons table (ie group_id) be set to 0 (or NULL) when deleting a group? If you do not, the person will try to belong to a group that does't exist.

The reason I ask is that this is default behavior in Cakephp. If you set dependent to true, it will delete the associated models, but if it's set to false it will leave the associated model untouched.

Answer 1

In a word, yes. Leaving the foreign key on the persons table would result in the loss of referential integrity within the database.

Answer 2

Yes, the foreign keys should be set to NULL (or 0, if this is your chosen 'no group' value) or you lose referential integrity. If your database supports it, you should be able to set an 'On delete' trigger or a cascade rule in your framework to enforce this. And the behaviour in CakePHP seems correct. If the value is dependent, then it should be removed on deletion. If it isn't dependent then you need to give extra behaviour logic as to the correct action to take (in this case, you want to set all values to NULL. In other cases, you may want to set to a 'default' group, etc)

Answer 3

> If you do not, the person will try to belong to a group that does't exist.

There is also a worse scenario: in the future a new group B may appear that will reuse the id of deleted group A. Then all former A group's users will be "magically" enlisted into new group B.