Auto add better constraint actions

[dereuromark]

This is a (multiple allowed):

• enhancement

• CakePHP Version: latest

What you did

Deleting a parent of child, that do not have "depending=>true" relationship.
As such, the foreign_key was still the old id, we just removed.
Now there is an invalid foreign key set that doesnt exist as row anymore.

Expected Behavior

When generating migrations (bake task and otherwise):

'delete' => 'SET_NULL' for addForeignKey()
should be auto-set for such "NULLable" fields that have no such depending constraint.
This would make sure the foreign_key here is set to NULL again and when we query for all that have no relation to that parent we would also find the right entities.

Example code

    $this->table('releases')
        ->addForeignKey('release_element_id', 'release_elements', ['id'], ['delete' => 'SET_NULL'])
        ->update();

For a DEFAULT NULL relation here.

[karmicdice]

This would make sure the foreign_key here is set to NULL again

Tried replicating this behaviour. What I noticed is. If the column is not nullable, it throws an error.

Please elucidate.

[dereuromark]

Yes, this is only meant for nullable colums to keep data integrity.
If not nullable, the parent delete must delete also the children (via constraint or domain layer logic / relation setup).

[dereuromark]

@markstory Do you think this could be done in a reasonable enough way?
Or is this too complicated?

[markstory]

I'm not sure I understand the scenario well. Is the situation that you have model relations defined, but no migration. Then when a migration is generated it should use model relations to infer the schema?

[LordSimal]

I am also confused about the scenario here.

As I understand it you already have tables present in your DB, so all you want to do is re-generate the migrations for those tables again with the current recommended config, right?

But it should add the ['delete' => 'SET_NULL'] part of the addForeignKey() call the FK column is nullable, right?
Thats at least how I would see it since we primarily use the DB schema to reflect what should get generated.

[dereuromark]

Yes, when baking a snapshot, it could fill in those missing things.
But yeah, maybe thats a bit out of scope.