CODEWITHSUNDEEP
javascriptnode.jspostgresqlsequelize.js
Mohnish M
Mohnish M

Reputation: 123

Sequelize and Postgres - foreign key constraint cannot be implemented

I have two tables, assets and services. The tables have a common column called 'asset_type'. I'm trying to associate the two using a foreign key. One asset could have many services (1-to-many).

I'm getting the above error when trying to create the services table. What am I doing wrong? Below are my code and the error log.

P.S: I'm new to sequelize and postgres.

Assets Table:

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
        });
    }

Services Table:

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'
        })
    }

Error Log:

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

Upvotes: 2

Views: 4681

Answers (1)

Anatoly
Anatoly

Reputation: 22758

Columns of both tables in a foreign key should be the same data type. You have primary key type - INTEGER and asset_type in Services - STRING.

Just change a data type of asset_type to INTEGER:

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

Upvotes: 5

Related Questions