使用NextAuth+Sequelize创建表时遇外键约束错误求助

我是NextJS新手，用NextAuth时卡了好几天，运行时提示创建accounts表失败，错误是「Foreign key constraint is incorrectly formed」，但这个表根本不在我的数据库模型里，怀疑是NextAuth配置有问题，附上错误日志和配置代码，求排查。

错误日志 #

Executing (default): CREATE TABLE IF NOT EXISTS `accounts` (`id` CHAR(36) BINARY , `type` VARCHAR(255) NOT NULL, `provider` VARCHAR(255) NOT NULL, `provider_account_id` VARCHAR(255) NOT NULL, `refresh_token` VARCHAR(255), `access_token` VARCHAR(255), `expires_at` INTEGER, `token_type` VARCHAR(255), `scope` VARCHAR(255), `id_token` TEXT, `session_state` VARCHAR(255), `user_id` CHAR(36) BINARY, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
Unable to connect to the database: Error
    at Query.run (webpack-internal:///(rsc)/./node_modules/sequelize/lib/dialects/mysql/query.js:58:29)
    at eval (webpack-internal:///(rsc)/./node_modules/sequelize/lib/sequelize.js:321:36)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async MySQLQueryInterface.createTable (webpack-internal:///(rsc)/./node_modules/sequelize/lib/dialects/abstract/query-interface.js:101:16)
    at async account.sync (webpack-internal:///(rsc)/./node_modules/sequelize/lib/model.js:1012:13)
    at async Sequelize.sync (webpack-internal:///(rsc)/./node_modules/sequelize/lib/sequelize.js:386:17)
    at async eval (webpack-internal:///(rsc)/./database/sequelize.js:18:9) {
  name: 'SequelizeDatabaseError',
  parent: Error: Can't create table `tms`.`accounts` (errno: 150 "Foreign key constraint is incorrectly formed")
      at Packet.asError (webpack-internal:///(rsc)/./node_modules/mysql2/lib/packets/packet.js:647:21)
      at Query.execute (webpack-internal:///(rsc)/./node_modules/mysql2/lib/commands/command.js:25:32)
      at Connection.handlePacket (webpack-internal:///(rsc)/./node_modules/mysql2/lib/connection.js:427:40)
      at PacketParser.eval [as onPacket] (webpack-internal:///(rsc)/./node_modules/mysql2/lib/connection.js:83:18)
      at PacketParser.executeStart (webpack-internal:///(rsc)/./node_modules/mysql2/lib/packet_parser.js:75:26)
      at Socket.eval (webpack-internal:///(rsc)/./node_modules/mysql2/lib/connection.js:90:31)
      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) {
    code: 'ER_CANT_CREATE_TABLE',
    errno: 1005,
    sqlState: 'HY000',
    sqlMessage: 'Can\'t create table `tms`.`accounts` (errno: 150 "Foreign key constraint is incorrectly formed")',
    sql: 'CREATE TABLE IF NOT EXISTS `accounts` (`id` CHAR(36) BINARY , `type` VARCHAR(255) NOT NULL, `provider` VARCHAR(255) NOT NULL, `provider_account_id` VARCHAR(255) NOT NULL, `refresh_token` VARCHAR(255), `access_token` VARCHAR(255), `expires_at` INTEGER, `token_type` VARCHAR(255), `scope` VARCHAR(255), `id_token` TEXT, `session_state` VARCHAR(255), `user_id` CHAR(36) BINARY, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;',
    parameters: undefined
  },
  original: Error: Can't create table `tms`.`accounts` (errno: 150 "Foreign key constraint is incorrectly formed")

我的NextAuth配置代码 #

import NextAuth from "next-auth/next"
import CredentialsProvider  from "next-auth/providers/credentials";
import SequelizeAdapter from "@auth/sequelize-adapter"
import sequelize  from  "../../../../database/sequelize"
import bcrypt from 'bcrypt'
import User from '../../../../database/models/user'

export const authOptions = ({
  adapter: SequelizeAdapter(sequelize),
  providers: [
    CredentialsProvider({
        name: "credentials",
        credentials:{
            email:{label: "email", type:"email", placeholder:"example@example.com" },
            password:{label:"password", type:"password"}
        },
        async authorize(credentials) {
          
          const { email, password } =  credentials
          console.log(credentials)
          const user = await User.findOne({ where: { email: email } });
          console.log("this is user" + user)
          if (!user) {
            throw new Error("Invalid username and password");
          }
        
          const isMatch = await bcrypt.compare(password, user.password);
          if (!isMatch) {
            throw new Error("Invalid username and password");
          }
          
          if(user && isMatch){
            return user
          }
        }
    })
  ],
  session: {
    strategy: "jwt",
    maxAge: 30 * 24 * 60 * 60, // 30 days
  },
  pages: {
    signIn: '/',

  },
  callbacks: {

    async jwt({token, user, account, profile, email, credentials}){
      console.log(token)
      console.log(user)
      console.log(account)
      console.log(profile)
      console.log(email)
      console.log(credentials)
      if(user){
        token = user.token
      }return token
    },
    async session({session, token}){
      if(token){
        session.token = token
      }
      return session
    },
  },
  secret: process.env.NEXTAUTH_SECRET
})

const handler = NextAuth(authOptions)
export { handler as GET, handler as POST }

核心原因 #

SequelizeAdapter会自动生成NextAuth依赖的表（包括accounts、sessions、verification_tokens等），但你的自定义User模型和Adapter期望的users表结构不匹配，或者表的创建顺序错误，导致外键关联失败：

• Adapter生成的accounts表中user_id字段为CHAR(36) BINARY（UUID类型），如果你的User模型的id字段类型（比如自增整数）和这个不一致，就会触发外键约束错误。
• 如果users表还未创建就尝试创建accounts表，数据库会因为找不到依赖的users.id字段而报错。

具体修复步骤 #

1. 统一User模型的ID类型 #

确保你的User模型的id字段是CHAR(36)类型的UUID，和Adapter生成的user_id完全匹配。示例修正后的User模型：

import { Model, DataTypes } from 'sequelize';
import sequelize from '../sequelize';

class User extends Model {}

User.init({
  id: {
    type: DataTypes.CHAR(36),
    allowNull: false,
    primaryKey: true,
    defaultValue: DataTypes.UUIDV4 // 自动生成UUID
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false
  }
}, { 
  sequelize, 
  modelName: 'user',
  tableName: 'users' // 确保表名为users，和Adapter的预期一致
});

export default User;

2. 调整数据库同步顺序 #

修改你的数据库初始化文件（比如database/sequelize.js），先同步User模型，再同步所有模型，确保users表先存在：

import sequelize from './sequelize';
import User from './models/user';

async function syncDatabase() {
  // 先同步User模型，确保users表存在且结构正确
  await User.sync({ alter: true });
  // 同步所有模型（包括Adapter生成的accounts等表）
  await sequelize.sync({ alter: true });
}

syncDatabase();

3. 可选：移除SequelizeAdapter（纯JWT模式） #

因为你使用的是CredentialsProvider且自己处理了用户验证，如果不需要NextAuth自动管理accounts等表，可以直接移除Adapter配置，改用纯JWT模式，这样就不会自动创建这些表了：

export const authOptions = {
  // 移除adapter配置
  providers: [
    CredentialsProvider({
      // 你的现有配置...
    })
  ],
  session: { strategy: "jwt" },
  pages: { signIn: '/' },
  callbacks: {
    // 你的现有回调配置...
  },
  secret: process.env.NEXTAUTH_SECRET
}

4. 确认数据库引擎 #

确保你的数据库使用InnoDB引擎（错误日志中已显示为InnoDB，但仍需确认），MyISAM不支持外键约束，会导致类似错误。

内容的提问来源于stack exchange，提问作者tsacarm