Sequelize and Postgres - foreign key constraint cannot be implemented

Question

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

Anatoly · Accepted Answer · 2021-01-02 12:50:24Z

5

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,
        },

answered Jan 2, 2021 at 12:50

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mohnish M Over a year ago

But the 'asset_type' column is supposed to be a string. Is there any other way?

Anatoly Over a year ago

How so? The data type should be the same because values on both sides should be the same. You mean you wish to store asset_type being INTEGER as a string value in Services?

Mohnish M Over a year ago

I'd like to store data as a string in the asset_type column (for ex: "AirCon", "TV", "Laptop" etc) How can I do this?

Anatoly Over a year ago

That's right you store these values in Asset table and not in Services. Services should just have a link field to Asset like asst_type_id: { type: DataTypes: INTEGER, allowNull: false } and you sould change assiciation like this: Service.belongsTo(models.Asset, { foreignKey: 'asset_type_id', as: 'asset' })

Mohnish M Over a year ago

I figured it out. Entering the pk of the asset in the asset_type column worked. Thanks for sharing your knowledge again!

|

Collectives™ on Stack Overflow

Sequelize and Postgres - foreign key constraint cannot be implemented

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related