sqlite self table FOREIGN KEY constraint failed with ON DELETE RESTRICT when drop()/sync({force:true})

[Suisse00]

Issue Description

I created a nested table that refers to itself.
When it contains data, trying to execute the .drop() or .sync({force: true}) will trigger a constraint failure from sqlite.

What are you doing?

const Sequelize = require('sequelize');

const sequelize = new Sequelize({dialect: 'sqlite', storage: 'database.sqlite'});

class MyTable extends Sequelize.Model {
}

MyTable.init({
    parentId: {
        type: Sequelize.INTEGER,
        onDelete: 'RESTRICT',
        references: {
            model: MyTable,
            key: 'id'
        }
    }
}, { sequelize, modelName: 'Alarms'});

sequelize
    .sync({force: true})
    // root
    .then(_ => MyTable.build().save()
        // child
        .then(rootModel => MyTable.build({parentId: rootModel.id}).save()))

    // BOOM
    .then(_ => sequelize.sync({force: true}))
    .catch(error => console.error(error));

will output

CREATE TABLE "Alarms" (
	"id"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"parentId"	INTEGER,
	"createdAt"	DATETIME NOT NULL,
	"updatedAt"	DATETIME NOT NULL,
	FOREIGN KEY("parentId") REFERENCES "Alarms"("id") ON DELETE RESTRICT
);

INSERT INTO "Alarms" (id, parentId, createdAt, updatedAt) VALUES(1, NULL, datetime('now'), datetime('now'));
INSERT INTO "Alarms" (id, parentId, createdAt, updatedAt) VALUES(2, 1, datetime('now'), datetime('now'));

What do you expect to happen?

Since I'm deleting the whole table, FK to the same table shouldn't trigger this FK constraint.

Note: I don't have a SQLite background, only a MSSQL/MYSQL. So this behavior is strange to me.

I did look into DROP TABLE documentation and they indeed talk about this behavior.

If foreign key constraints are enabled, a DROP TABLE command performs an implicit DELETE FROM command before removing the table from the database schema. [...] An implicit DELETE FROM does cause any configured foreign key actions to take place.

I could see why this ticket could be closed as won't do.

Environment

• Sequelize version: 5.21.1
• Node.js version: 10.13.0
• Operating System: Win64

Issue Template Checklist

How does this problem relate to dialects?

• I think this problem happens regardless of the dialect.
• I think this problem happens only for the following dialect(s): sqlite
• I don't know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[papb]

Thanks for the report, can you please convert your code into a sequelize-sscce so that we can easily see it working on all dialects except sqlite?

[Suisse00]

Done ( sequelize/sequelize-sscce#18 )
Out of the 4 dialects only the SQLite failed.

[papb]

Thanks! Great SSCCE.

Do you have any suggestion on how Sequelize should handle this? Should Sequelize remove the foreign key constraint before deleting?

[papb]

I've tagged this as a feature because it would be something extra that Sequelize is doing since SQLite doesn't do it by itself.

[Suisse00]

I'm new to both SQLite and sequelize so I can't suggest the best approach.

I did Google a little bit before submitting my suggestions and they did make no sense... SQLite doesn't support to drop a column or FK... oh... crap...

So the only suggestion that left on my list is: Parse the FK constraint and delete rows ourselves in the right order. To this suggestion, I added the note (before googling): "at this point, it should probably be a user addon in a different NPM or the user should switch to ON DELETE CASCADE"

So I totally agree with you about the feature label! (if you still go ahead, and if there is nobody that already did this somewhere).

After thinking about it my recommendation would be: Close the issue. I should have know SQLite limit.

[papb]

@Suisse00 I see... Thanks :)

I will leave it open because perhaps it might be interesting to try to implement a workaround for this in Sequelize, however it would be much better if SQLite supported this natively; so I will tag this as a low priority feature.

[romannep]

For some cases could help turning off foreign key constrait

await sequelize.query('PRAGMA foreign_keys = false;');
sequelize.sync({...});
await sequelize.query('PRAGMA foreign_keys = true;');

[papb]

@romannep Hmmm, perhaps this could be built-in the force: true option for SQLite? Do you think it's too risky? I think it could be done, force: true is already risky anyway

[louy2]

I think it is reasonable to build this into force: true for SQLite.

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[silencej]

https://stackoverflow.com/a/5987838/6191913
SQLite 2021-03-12 (3.35.0) now supports DROP COLUMN.

Shall we update this accordingly?