foreign key mismatch - while executing PRAGMA foreign_key_check

[y0zong]

I'm not familiar with sqlite, so I don't know it's from sqlite or grdb foreign key mismatch - "songCollection" referencing "collection" - while executing PRAGMA foreign_key_check, please help

try db.create(table: "song") { t in
                t.primaryKey("id", .integer).notNull()
                t.column("name", .text).notNull()
                t.column("cover", .text).notNull()
                t.column("type", .text).notNull()
                t.belongsTo("composer", inTable: "artist", indexed: true).notNull()
                t.belongsTo("reference", inTable: "song", indexed: true)
            }
            
            try db.create(table: "collection") { t in
                t.column("id", .integer)
                t.autoIncrementedPrimaryKey("localId")
                t.column("name", .text).notNull()
                t.column("cover", .text)
                t.column("slogan", .text)
                t.belongsTo("creater", inTable: "person", indexed: true).notNull()
            }
            
            try db.create(table: "songCollection") { t in
                t.belongsTo("song", inTable: "song", indexed: true).notNull()
                t.column("collectionId", .integer).references("collection", column: "id",  onDelete: .cascade).indexed()
                t.column("localCollectionLocalId", .integer).references("collection", column: "localId",  onDelete: .cascade).indexed()
            }

[groue]

hello @y0zong,

I'm sure the error message comes with the SQL statement that was executed, it will help identifying the issue!

[y0zong]

THANKS for reply really fast!
I delete the app and rebuild it with same error. so, I want to create some table with relationships blow, how can I do it in grdb way?

CREATE TABLE song (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT,
    cover TEXT,
    type TEXT,
    ...
)

CREATE TABLE collection (
    id INTEGER,
    localId INTEGER PRIMARY KEY AUTOINCREMENT,
    cover TEXT,
    type TEXT,
    ...
)

--  many-to-many, may be it's better to create songCollection & localSongCollection separately
CREATE TABLE songCollection (
    songId INTEGER REFERENCES song(id),
    collectionId INTEGER REFERENCES collection(id),
    localCollectionLocalId INTEGER REFERENCES collection(localId),
)

[groue]

I don't know. Please provide a reproducing sample code. Some code I can paste and run so that I can see the error message. If it does not run when I paste it, I can't help.