Change parent_id to null in child model when parent deleted

That is a bad idea. That kind of database state get's messy. Either delete child with the parent or add SoftDelete to both.

via a foreign key constraint in your migrations, you can add

$table->foreign('parent_id')->references('id')->on('parent_table')
      ->onDelete('set null');

@bugsysha possible use case is for example, employee belongs to manager (also an employee). Manager leaves, employee should not reference non existing manager, nor should they be deleted.

@snapey the manager should be replaced with another, not leave those employees with blank manager field. Also I guess that manager has someone on top to report to also. And let's say that we go all to the top where we have a CEO who does not report to anyone, then he can have himself as a manager. So in my mind never leave foreign keys empty.

If you do not have teams in your app, but use manager to see who belongs to which team like in your example, then let's say that two managers leave, you will not have a way to figure out who belongs to which team later on when you have to declare new manager for specific "team".

@bugsysha Sometimes our logical thinking does not apply to the people we make our applications for I learned recently. We can tell/advice them how things should be done but if they don't agree there has to be a compromise to be made.

The manager example does not seem logical to me too, but I wouldn't be surprised if its being used.

@cruorzy that is the beauty and the process of learning 😁

Its just an example. Sometimes children can be parentless, in real life and in FK relationships.

Sometimes children can be parentless

Not in my apps :D

@snapey

So what you do when you have that much children parentless and you try to make use of that data? Or you want to assign same "group" to new parent?

Something can belong to nothing. Its an optional relationship.

Its also trivial to create a function that lists parentless objects and asks the user if they want to attach them.

• user might belong to a team (or not)
• invoice might belong to a quotation (or not)
• menu item might belong to parent menu (or not)

etc, etc

@snapey don't get me wrong. I just trying to figure out a different point of view. Can you elaborate a bit more? Where would you keep the records to which parent some children belong to when the parent is deleted and you put nulls for that relationship? And to repeat myself, how would you handle having two parents being deleted and later on to not mix up children when new parent is created?

@bugsysha

elaborating on the above examples;

User can register to use an application. At some point they may create a team, and then their team_id field can be populated. So there is a point initially where the team does not yet exist. They could also choose to delete the team. It would be strange workflow to say that the user must create a new team before they could delete the original.

A Quotation in a system, can generate many invoices. It may also be possible to generate invoices for services that were not quoted. So an invoice might belong to a quote, or it might not. If the quotation is deleted for some reason, it would be unwise to reassign the invoice to another quotation or to leave it with the original quote id.

A collection of menu items might have some form of hierarchy allowing nav, sub-nav and sub-sub-nav. Each menu item can potentially have a parent menu item. When moving menu items around, the parent_id field might be populated / unpopulated, and if the parent menu item is deleted, it does not mean that the sub-menu item is no longer required.

I hope you understand my POV

• FKs can be nullable
• domain logic might mean that its perfectly acceptable to detach a model from a parent

I agree with @snapey

It makes no sense to me that every foreign key use cases would imply that they should never be null. @snapey gave some excellent examples. I'm waiting for your point of you @bugsysha because I feel like your reasoning might make me understand your side. This is an interesting debate.

@snapey business intelligence team at the company I worked for always said that kind of data is pretty useless from their point of view. Also at one company there were a bunch of nullable foreign keys and it became a living mess. So from those two reasons I'm doing everything to avoid nullable foreign keys and it seems like a good practice. I haven't had any issues so far. It is far easier for me to manage data with soft deletes than to worry about orphans.

When ever I had a team in my app then relationship between team and user was many to many. And usually that team was billable so there was no issues with nullable foreign keys. I'm trying to think of a situation where team has many users and what ever comes to my mind then the user is a second class citizen and can not work without team.

Regarding Quotation and Invoices, since english is not my first language I'm not sure I understand the difference.

As for the menu items example, yeah probably in most cases it would make sense to have nullable foreign key, but since I can model that so that top parent points to itself then there is still a way for me to easily avoid that so when the top level menu item is deleted I do not suddenly put a bunch of nested menu items on top menu since my logic would probably be something like fetch all menu items as parents if they have a null for parent key.

@piljac1 sorry for late response. I've missed it somehow. What I haven't put in this thread is that I hate having business/application logic outside of PHP domain and in this case it is handled by the database. I know it is a long shot but what if you change your database, or use something different for testing, or who knows what. There is probably a bunch of issues which can cause pain points for having split approach which I can not think of right now.

@bugsysha , If we follow your reasoning, how you implement this : I have a model serie and episode like in laracasts. I would like to add a episode in serie but when serie is deleted, i would like that the episode become a lesson.

@sylvanotombo so lesson is also an episode just that it does not have a relationship to series? I would probably create a series called standalone lessons. Easy way to group them and you can have series like description for that bunch of videos.

What I'm trying to say is that I avoid nullable foreign keys as much as possible. I'm sure there is a scenario where that is required.

Two key points I'm trying to make here:

1. I would never assign something to some foreign model (series) and then delete that foreign model (series) without reassigning those depending models (episodes) to new foreign model (series). Once I delete foreign model (series) then depending models (episodes) also become irrelevant unless reassigned first to some other foreign model.

2. Also I would keep all my application/business logic in PHP and not in the database. So the PHP would handle that like it does with Soft Deletes so you can easily track them down, have them excluded by default and include them if you want. You never loose sight why they were made or who owns them.

@bugsysha, Thanks for your reply. I think for my case, your idea might be the best approach.

@sylvanotombo thanks. If you do not mind please select it as "best answer".