Database referential integrity with Doctrine

Here could be extensive AI-generated introduction about Database referential integrity, but not today. Suppose you know what is it. The subject of this article is how you can deal with it using Doctrine.

Actually Doctrine can deal with it in several ways and sometimes it's not so obvious as we want.

Example #1

Suppose we have a blog, entities User and Post, and every post has some author which is one of users.

I the most simple case we create unidirectional 'many-to-one' association

#[ORM\Table(name: 'posts')]
class Post {
    ...
    #[ORM\ManyToOne(targetEntity: User::class)]
    #[ORM\JoinColumn(nullable: false)]
    private User $author;
    ...
}

On MySQL we will get the post table with one foreign key. Everything is easy

create table posts
(
    ...
    author_id  int  not null,
    constraint FK_58A92E65F675F31B
        foreign key (author_id) references users (id)
);

create index IDX_58A92E65F675F31B
    on posts (author_id);

So what we have now?

Now we have a constraint on DB level, which blocks any attemts to delete user which is linked as an author to some blog post

[23000][1451] 
Cannot delete or update a parent row: a foreign key constraint fails 
(`database`.`posts`, CONSTRAINT `FK_58A92E65F675F31B` FOREIGN KEY (`author_id`) REFERENCES `user` (`id`))

There are no difference do you try to delete it directly in database or through your application. It's database constraint and it works this way.

Example #2

So now we can't delete a guy who has a post in our blog. But what if we must be able do it sometimes?

First of all we have to decide what to do with posts of deleted author? For example we could want to delete them too. I this case we can add onDelete option:

#[ORM\Table(name: 'posts')]
class Post {
    ...
    #[ORM\ManyToOne(targetEntity: User::class)]
    #[ORM\JoinColumn(nullable: false, onDelete: 'CASCADE' )]
    private User $author;
    ...
}

In this case our foreign key will get on delete cascade

create table posts
(
    ...
    author_id    int          not null,
    constraint FK_58A92E65F675F31B
        foreign key (author_id) references users (id)
            on delete cascade
);

create index IDX_58A92E65F675F31B
    on posts (author_id);

This means if we delete user all his posts will be removed too.

But remember, some posts can have comments. Do you want remove them too? All the comments to the posts written by this guy? In this case you probably should the same cascade trick with comments

One important thing here is that all this logic is done by DB server. Doctrine does nothing with it. From doctrine level everything you do is just about removing user.

Example #3

Let's think about application logic. Is it so bad if some post will not have author? If the only effect there will be no author on the page from some point of view it could be acceptable. So we can just clear author field in this case.

In this case we need to make it nullable first, and then change onDelete parameter

    #[ORM\JoinColumn(nullable: true, onDelete: 'SET NULL' )]
    private ?User $author = null;

In this case our field in database will look like this:

    constraint FK_58A92E65F675F31B
        foreign key (author_id) references users (id)
            on delete set null

And this is enough.

Just want to note again, all these cases will be handled by DB server. Doctrine has nothing to do here

Example #4

Let's step back and now we want to remove posts with author. We want the same effect, but using Doctrine. Here we will need bidirectional association

So in Post entity we put this:

  #[ORM\ManyToOne(targetEntity: User::class, inversedBy: "posts")]
  #[ORM\JoinColumn(nullable: false)]
  private ?User $author = null;

And in User entity this:

  #[ORM\OneToMany(targetEntity: Post::class, mappedBy: 'author', cascade: ['remove'])]
  private Collection $posts;

Of course we'll have to add constructor, and getters/setters if you need. But in general this code will work!
It acts like onDelete: 'CASCADE'. The main difference is that now Doctrine does all the things.

Actually now Doctrine generates a bunch of sql-queries to delete user, something like this:

-- fetch post IDs for the author
SELECT post.id FROM posts as post WHERE post.author_id = AUTHOR_ID;
-- delete posts one by one
DELETE FROM posts WHERE id = POST_ID_1;
DELETE FROM posts WHERE id = POST_ID_2;
...
DELETE FROM posts WHERE id = POST_ID_N;
-- finally delete the author
DELETE FROM users WHERE users.id = AUTHOR_ID;

Example #5

Absolutely the same effect will be using orphanRemoval: true

Just use it in User entity

  #[ORM\OneToMany(targetEntity: Post::class, mappedBy: 'author',  orphanRemoval: true)]
  private Collection $posts;

Here are described the most common cases.