Failure on foreign key constraint: on delete cascade

💻 Development app dev
, ,
1

I have created a migration file like this. I checked phpMyAdmin, table oc_qlcb_user, Structure/Relation view and can’t see any constraints. I also tried to delete the record on oc_qlcb_position. As a result, in the oc_qlcb_user table,records with position_id equal to that id are not deleted but only the position_id value is changed to null.

class Version1000Date20240429192142 extends SimpleMigrationStep
{
    /**
     * @param IOutput $output
     * @param Closure $schemaClosure The `\Closure` returns a `ISchemaWrapper`
     * @param array $options
     * @return null|ISchemaWrapper
     */
    public function changeSchema(
        IOutput $output,
        Closure $schemaClosure,
        array $options
    ): ?ISchemaWrapper {
        /** @var ISchemaWrapper $schema */
        $schema = $schemaClosure();

        //qlcb_position

        if (!$schema->hasTable("qlcb_position")) {
            $table = $schema->createTable("qlcb_position");
            $table->addColumn("position_id", "string", [
                "notnull" => true,
                "length" => 64,
                "default" => "",
            ]);
            $table->addColumn("position_name", "string", [
                "notnull" => true,
                "length" => 255,
            ]);
            $table->setPrimaryKey(["position_id"]);
        }

        // qlcb_user

        if (!$schema->hasTable("qlcb_user")) {
            $table = $schema->createTable("qlcb_user");
            $table->addColumn("qlcb_uid", "string", [
                "notnull" => true,
                "length" => 64,
                "default" => "",
            ]);
            $table->addColumn("full_name", "string", [
                "notnull" => true,
                "length" => 64,
            ]);
            $table->addColumn("date_of_birth", "date", [
                "notnull" => false,
            ]);
            $table->addColumn("position_id", "string", [
                "notnull" => true,
                "length" => 64,
                "default" => "",
            ]);
            $table->setPrimaryKey(["qlcb_uid"]);
            $table->addForeignKeyConstraint(
                "qlcb_position",
                ["position_id"],
                ["position_id"],
                ["onDelete" => "CASCADE"]
            );
        }
        return $schema;
    }
}
2

Hello.

Just to clarify: what is working and what is not working? Did you manage to successfully mortgage to this state such that this migration works as extended?

The foreign key is associated with a position table. This is pre-existing?

Just s generic hint: it is not intended to use the glory details of doctrine extensively. You have no control over the doctrine version, so it is Rather hard to have a clear documentation on what is possible and what not. It was asked here: Wait, you misplaced the grail? Cannot find Doctrine DBAL API docs - #2 by ChristophWurst

Christian

3

I can create those tables successfully with predefined configuration except the foreign key constraint.

4

I have read the documents at Storage and database — Nextcloud latest Developer Manual latest documentation. I don’t find any instructions for the constraint. Do you mean I need to find another way to create constraints between two tables, such as using phpMyAdmin? If I can do all this via phpMyAdmin like creating, modifying tables, etc., then why do I need migration files?

7

No, please do not use phpMyAdmin to change the DB. The idea of the migrations is that any other installation can be brought to the exact same situation (in terms of structure not data) as your dev instance.

You would break this if you changed things in the DB manually. (Honestly, I did it as well but very carefully and with a backup prepared for restoring to a defined state. But this is advanced usage and you shoul dknow what you are doing.)

No the point is to not rely on all glory details of the DB system. NC is able to be installed on MySQL, MariaDB, PostgreSQL, Orace, SQLite, and possibly more in the future. So, there might be features that only a subset of these is capable. Keep this in mind when working with migrations: These must be generic enough to be worked on by all DB systems.

The point is: What do you need the constraints for? You can do the same by using transactions and correctly carrying out the cascade as the SQL server would do. Is it just convenience?

Christian

8

What I want is when I delete a record on a table, all related records in the dependent table should be deleted

9

As I said, you can always do that yourself with just a few lines of SQL code. This code would be perfectly with the scope of the NC DB interface.

I am aware that this is somewhat more verbose and a little bit slower than letting the DB server handle the removal. However, it makes debugging/code reading easier and more explicit. It should be just something like this:

$qb = $this->db->getQueryBuilder();
// Remove users associated with position (fake ON DELETE CASCADE)
$qb->delete('qlcb_user')
    ->where($qb->expr()->eq('position_id', $positionIdToRemove))
    ->executeStatement();
// Remove the actual entry from the positions table
$qb->delete('qlcb_position')
    ->where($qb->expr()->eq('position_id', $positionIdToRemove))
    ->executeStatement();

I did not test this code but it seems rather straight forward. Please check the syntax and context as well as the tables names and the corresponding logic as I am just guessing from the table names what you want to achieve.

Doesn’t this solve your problem?
Christian

1 Like
10

The first parameter is the foreign table. Are you sure about position because you wrote unit in the text and showed the scheme for unit?

11

Thank you. It’s a spelling error. I’ve just edited my post.

1 Like
12

That’s also a good choice. Thank you, Chris. But in summary, can I say that the migration file does not support foreign key constraints? I checked through the Nexctcloud database and couldn’t see anything about that.

13

No.

While foreign key constraints are not often used in our codebase, they should still work if we expose that functionality. The addForeignKeyConstraint something provided by doctrine/dbal. I don’t see why it should not work.

The following migration is using addForeignKeyConstraint and the relations are created properly on my dev setup.

image
image1313×926 57.8 KB

14

I would say, it is in general possible as the nextcloud server provides the doctrine objects right now for the migrations. But as already written, it is not adviced to rely on these things. Especially, as there isa simple solution.

Christian

15

I see. I’m gonna try your suggestion. :smiling_face:

1 Like
16

Thanks for providing. I’m gonna to make some changes to my migration files. I’m not familiar with Doctrine DBAL. I’ve just checked my logs and see that there’s an error about the foreign key constraint. Despite that, my tables are still created successfully.