使用 sequelize 将数据添加到表中时缺少列

Missing column while adding data into table using sequelize

提问人：user21100383 提问时间：3/19/2023 最后编辑：marc_suser21100383 更新时间：3/19/2023 访问量：533

问：

我正在使用 nodejs Web 应用程序，刚刚开始，我创建了一个 Postgresql 数据库并使用 sequelize 添加了一些表。

在这个过程中，我创建了一个表，该表包含列，默认情况下已创建和列。现在，如果我尝试使用以下代码将数据添加到表中：QuestionOptionsTitle:stringQueId:foreign keyidcreatedAtupdatedAt

app.post("/submitOptions", async (request, response) => {
  console.log("Options request body", request.body);
  try {
    for (const key in request.body) {
      if (key.startsWith("q-")) {
        const questionIDfromRequestBody = key.split("-")[1];
        const optionIndex = key.split("-")[3];
        const optionValueKey = `q-${questionIDfromRequestBody}-option-${optionIndex}`;
        const optionValue = request.body[optionValueKey];
        console.log(optionValue);
        const option = await QuestionOptions.addOption({
          Title: optionValue,
          QueId: questionIDfromRequestBody
        });
      }
    }
    // Store the added questions in a session variable
    return response.redirect(`/addElectors`);
  } catch (error) {
    console.log(error);
    error.errors.forEach((element) => {
      request.flash("error", element.message);
    });
    response.redirect("/addOptions");
  }
});

我有一个这样的请求正文：

Options request body [Object: null prototype] {
  _csrf: '550a1baf-04d2-46b1-9123-3977034d65c2',
  'q-11-option-1': '11',
  'q-11-option-2': '12',
  'q-12-option-1': '21',
  'q-12-option-2': '22'
}

但我得到一个

null 值将插入到“标题”列中

Executing (default): INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";
Error
    at Query.run (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/dialects/postgres/query.js:50:25)
    at /home/hemanth/Voting-app-master/node_modules/sequelize/lib/sequelize.js:315:28
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresQueryInterface.insert (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/dialects/abstract/query-interface.js:308:21)
    at async QuestionOptions.save (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/model.js:2432:35)
    at async QuestionOptions.create (/home/hemanth/Voting-app-master/node_modules/sequelize/lib/model.js:1344:12)
    at async /home/hemanth/Voting-app-master/app.js:269:24 {
  name: 'SequelizeDatabaseError',
  parent: error: null value in column "Title" of relation "QuestionOptions" violates not-null constraint
      at Parser.parseErrorMessage (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 275,
    severity: 'ERROR',
    code: '23502',
    detail: 'Failing row contains (6, null, 2023-03-19 04:39:56.652+00, 2023-03-19 04:39:56.652+00, 11).',
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: 'public',
    table: 'QuestionOptions',
    column: 'Title',
    dataType: undefined,
    constraint: undefined,
    file: 'execMain.c',
    line: '1883',
    routine: 'ExecConstraints',
    sql: 'INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";',
    parameters: [
      '2023-03-19 04:39:56.652 +00:00',
      '2023-03-19 04:39:56.652 +00:00',
      '11'
    ]
  },
  original: error: null value in column "Title" of relation "QuestionOptions" violates not-null constraint
      at Parser.parseErrorMessage (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/home/hemanth/Voting-app-master/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 275,
    severity: 'ERROR',
    code: '23502',
    detail: 'Failing row contains (6, null, 2023-03-19 04:39:56.652+00, 2023-03-19 04:39:56.652+00, 11).',
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: 'public',
    table: 'QuestionOptions',
    column: 'Title',
    dataType: undefined,
    constraint: undefined,
    file: 'execMain.c',
    line: '1883',
    routine: 'ExecConstraints',
    sql: 'INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";',
    parameters: [
      '2023-03-19 04:39:56.652 +00:00',
      '2023-03-19 04:39:56.652 +00:00',
      '11'
    ]
  },
  sql: 'INSERT INTO "QuestionOptions" ("id","createdAt","updatedAt","QueId") VALUES (DEFAULT,$1,$2,$3) RETURNING "id","Title","createdAt","updatedAt","QueId";',
  parameters: [
    '2023-03-19 04:39:56.652 +00:00',
    '2023-03-19 04:39:56.652 +00:00',
    '11'
  ]
}
/home/hemanth/Voting-app-master/app.js:279
    error.errors.forEach((element) => {
                 ^

TypeError: Cannot read properties of undefined (reading 'forEach')
    at /home/hemanth/Voting-app-master/app.js:279:18
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

Node.js v18.13.0
[nodemon] app crashed - waiting for file changes before starting...

我注意到在错误的第一行中它说“正在执行（默认）： INSERT INTO ”QuestionOptions“ （”id“，”createdAt“，”updatedAt“，”QueId“） VALUES （DEFAULT，$1，$2，$3） 返回 ”id“，”Title“，”createdAt“，”updatedAt“，”QueId“;”

它缺少列，但返回时它说“返回”id“，”Title“，”createdAt“，”updatedAt“，”QueId“;”包含列。Title("id","createdAt","updatedAt","QueId")Title

我不知道该怎么做，我尝试从头开始删除并创建一个新表，但它的行为是一样的。

我期望在表的 Title 列中有一些值而不是 null，我什至添加了约束。QuestionOptionsnotNull

sql node.js postgresql null sequelize-cli

评论

0赞 user21100383 3/19/2023
这是我的项目的 git 链接，github.com/20N31A0318/Voting-app

答：

0赞 BuckX 3/19/2023 #1

错误“关系'QuestionOptions'的'Title'列中的空值违反了非空约束”意味着'QuestionOptions'表中的'Title'字段设置为非空值，并且正在将空值插入到该字段中。

错误消息告诉您，将 null 值插入到“QuestionOptions”表的“Title”列中存在问题。在创建新的“QuestionOption”对象时，您需要确保为“Title”字段传递非 null 值。

检查要传递给“Title”字段的“optionValue”的值。它似乎是空的，导致错误。在尝试创建“QuestionOption”对象之前，可能需要添加一些验证以确保“optionValue”不为 null。

此外，如果您希望能够将 null 值插入到该字段中，请检查“QuestionOptions”表中的“Title”字段是否设置为允许 null 值。如果该字段未设置为允许 null 值，则在创建新的“QuestionOption”对象时，需要为“Title”字段传递非 null 值。

评论

0赞 user21100383 3/19/2023
我尝试打印“optionValue”的值，输出是这样的：是我得到的optionValue的输出。它们不是 null。"Options request body [Object: null prototype] { _csrf: '150021c9-b377-4dc3-ba18-88ee4322e510', 'q-13-option-1': '11', 'q-13-option-2': '12', 'q-14-option-1': '21', 'q-14-option-2': '22' } 11 12 21 22"11 12 21 22

上一个：对于Postgres中的非空列，IS DISTINCT FROM vs NOT EQUAL？

下一个：SQL COALESCE 函数不适用于 GORM