在外键列中设置值时出现问题

Question

我使用squeelize.js和SQLite-database，遇到了一个为外键设置值的问题。我有以下代码：

const MessageModel = sequelize.define('MessageModel ', {
    uuid: DataTypes.STRING,
    authorId: DataTypes.STRING,
    // ... other props
  }, {});

const TodoModel = sequelize.define('TodoModel', {
    ownerId: DataTypes.STRING,
    status: {
      type: DataTypes.STRING,
      defaultValue: 'pending'
    }
  }, {});

TodoModel.belongsTo(MessageModel , {
    foreignKey: {
        name: 'messageId',
        field: 'messageId',
        allowNull: false
    },
    targetKey: 'uuid'
});

MessageModel.create({
   uuid: 'testUUIDForExample'
   // other props
}).then(message => {

   console.log(`Message's created successful`);

   TodoModel.create({
      ownerId: 'id-string',
      status: 'test-status',
      messageId: 'testUUIDForExample'
   })
})

Sequelize在DB中创建MessageModel-row，但当它试图生成TodoModel时，它会出现以下错误：

DatabaseError: SQLITE_ERROR: foreign key mismatch - "TodoModel" referencing "MessageModel "
    at Query.formatError (C:\Users\lrsvo\web-development\projects\platoon-web-electron\node_modules\sequelize\lib\dialects\sqlite\query.js:432:16)
    at Query._handleQueryResponse (C:\Users\lrsvo\web-development\projects\platoon-web-electron\node_modules\sequelize\lib\dialects\sqlite\query.js:77:18)
    at afterExecute (C:\Users\lrsvo\web-development\projects\platoon-web-electron\node_modules\sequelize\lib\dialects\sqlite\query.js:260:31)
    at Statement.errBack (C:\Users\lrsvo\web-development\projects\platoon-web-electron\node_modules\sqlite3\lib\sqlite3.js:16:21)

Err.original.message: "SQLITE_ERROR: foreign key mismatch - "TodoModel" referencing "MessageModel"

生成得SQL：

"INSERT INTO `TodoModel` (`id`,`ownerId`,`status`,`createdAt`,`updatedAt`,`messageId`) VALUES (NULL,$1,$2,$3,$4,$5);"

我的TodoModel表如下所示：

CREATE TABLE "TodoModel" (
    "id"            INTEGER PRIMARY KEY AUTOINCREMENT,
    "ownerId"   VARCHAR(255),
    "status"    TEXT DEFAULT 'pending',
    "createdAt" DATETIME NOT NULL,
    "updatedAt" DATETIME NOT NULL,
    "messageId" VARCHAR(255) NOT NULL,
    FOREIGN KEY("messageId") REFERENCES "MessageModel"("uuid") ON DELETE NO ACTION ON UPDATE CASCADE
);

我在SQLite中使用了“sequelize”：“^5.1.0”。

MyConfig文件：

const Sequelize = require("sequelize");
const electron = require('electron');
const storagePath = electron.app.getPath('userData') + '/plt.db';

module.exports = {
  development: {
    dialect: "sqlite",
    storage: storagePath,
    username: null,
    password: null,
    operatorsAliases: Sequelize.Op,
    define: { freezeTableName: true },
    query: { raw: true }, // Always get raw result
    logging: true,
  },
};

Answer 1

这里有许多东西。首先，如果要在MessageModel上使用uuid作为主键，则必须定义它，否则将有一个默认的id字段。

const MessageModel = sequelize.define('MessageModel ', {
  uuid:{ // if this is your primary key you have to define it
    type: DataTypes.STRING,  //there is also DataTypes.UUID
    allowNull: false,
    primaryKey: true,
    unique: true
  }, 
  authorId: DataTypes.STRING,
  // ... other props
}, {});

然后在TodoModel上，将messageid关联设置为Integer。要将其更改为string，必须在模型上定义字段，并在关联上将其用作外键。

const TodoModel = sequelize.define('TodoModel', {
  ownerId: DataTypes.STRING,
  status: {
    type: DataTypes.STRING,
    defaultValue: 'pending'
  },
  messageId: { //you also have to add the field on your model and set it as STRING, because on the association Sequelize by default is going to use INTEGER
    type: DataTypes.STRING,
    allowNull: false
  }
}, {});

TodoModel.belongsTo(MessageModel , {
  as: 'Message',
  foreignKey: 'messageId', // and you only set the foreignKey - Same name as your field above
});