javascript - Sequelize 和 Postgres - 无法实现外键约束

Question

我有两张 table ， Assets 和服务。这些表有一个名为“asset_type”的公共(public)列。我正在尝试使用外键将两者关联起来。一项 Assets 可以有许多服务(一对多)。
尝试创建服务表时出现上述错误。我究竟做错了什么？
下面是我的代码和错误日志。
PS:我是 Sequelize 和postgres的新手。
Assets 表:

const Asset = sequelize.define(
    'Asset', {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true
            },
        asset_category: {
            allowNull: false,
            type: DataTypes.STRING
        },
        asset_type: {
            allowNull: false,
            type: DataTypes.STRING,
        },
        asset_description: {
            allowNull: false,
            type: DataTypes.STRING
        }
    }, {
      paranoid: true,
      tableName: 'assets'
    }
    );

    Asset.associate = function(models) {
        Asset.hasMany(models.Service, {
            as: 'services',
            foreignKey: 'asset_type',
            onDelete: 'set null',
            onUpdate: 'set null',
            hooks: true
        });
    }

服务表:

const Service = sequelize.define(
      'Service',
      {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        asset_type: {
            allowNull: false,
            type: DataTypes.STRING,
        },
        service_type: {
            allowNull: false,
            type: DataTypes.STRING,
        }
      },
      { 
        paranoid: true,
        tableName: 'services'
      }
    );

    Service.associate = function(models) {
        Service.belongsTo(models.Asset, {
          foreignKey: 'asset_type',
          as: 'asset'
        })
    }

错误日志:

Executing (default): DROP TABLE IF EXISTS "services" CASCADE;
Executing (default): DROP TABLE IF EXISTS "assets" CASCADE;
Executing (default): DROP TABLE IF EXISTS "assets" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "assets" ("id"  SERIAL , "asset_category" VARCHAR(255) NOT NULL, "asset_type" VARCHAR(255) NOT NULL, "asset_description" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "deletedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'assets' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "services" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "services" ("id"  SERIAL , "asset_type" VARCHAR(255) NOT NULL REFERENCES "assets" ("id") ON DELETE SET NULL ON UPDATE SET NULL, "service_type" VARCHAR(255) NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "deletedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("id"));
Database connection failed: SequelizeDatabaseError: foreign key constraint "services_asset_type_fkey" cannot be implemented

Answer 1

外键中两个表的列应该是相同的数据类型。您有主键类型 - INTEGER和 asset_type在 Services - STRING .
只需将数据类型更改为 asset_type至INTEGER :

const Service = sequelize.define(
      'Service',
      {
        id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        asset_type: {
            allowNull: false,
            type: DataTypes.INTEGER,
        },