Sequelize sync and default value of foreign key throws invalid syntax error

12 Views Asked by At

I have an empty Postgres database and I want to declare two tables, one for clients and the other for statuses. The clients table will have a foreign key of client_status_id, which tracks the id field of the statuses table. I also want to make sure that when creating a new client, it always has a default of 1 for client_status_id, which is the id in statuses which will be linked to the "new client" status name.

export default (sequelize: Sequelize) => {
  const Clients = sequelize.define(
    'Clients',
    {
      client_id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      client_status_id: {
        type: DataTypes.INTEGER,
        defaultValue: 1,
        allowNull: false,
        references: {
          model: 'client_statuses',
          key: 'client_status_id'
        }
      },

My statuses table looks like this

export default (sequelize: Sequelize) => {
  const ClientStatuses = sequelize.define(
    'ClientStatuses',
    {
      client_status_id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      status_name: {
        type: DataTypes.TEXT,
        allowNull: false
      }
    },
    {
      tableName: 'client_statuses',
      timestamps: true
    }
  );
  return ClientStatuses;
};

My associations look like this:

Clients.belongsTo(ClientStatuses, {
  foreignKey: 'client_status_id'
});
ClientStatuses.hasMany(Clients, { foreignKey: 'client_status_id' });

When I try to run sequelize.sync({force: true}) I get the following error:

name: 'SequelizeDatabaseError',
parent: error: syntax error at or near "REFERENCES"

for sql statement

 sql: 'ALTER TABLE "clients" ALTER COLUMN "client_status_id" SET NOT NULL;ALTER TABLE "clients" ALTER COLUMN "client_status_id" SET DEFAULT 1 REFERENCES "client_statuses" ("client_status_id") ON DELETE NO ACTION ON UPDATE CASCADE;ALTER TABLE "clients" ALTER COLUMN "client_status_id" TYPE INTEGER;

Why is sequelize generating invalid sql syntax? As far as I understand this shouldn't be possible.

How can I fix this is issue? Do I need to ensure that a row is first created in with id of 1 in the client_statuses table before I can define a default value in my clients table?

0

There are 0 best solutions below