Relation constraints not being applied correctly

[tjenkinson]

What you are doing?

I have a couple of models and the belongsToMany relation for each of them, and am trying to set the onDelete rule so one is 'cascade' and the other is 'restrict'. Using postgres.

User.belongsToMany(Group, {through: 'userToGroup', onDelete: 'CASCADE'});
// do not allow a group to be deleted if it has references to users
Group.belongsToMany(User, {through: 'userToGroup', onDelete: 'RESTRICT'});

Then I do sequelize.sync().

What do you expect to happen?

In posrgres the constraints should be:

  CONSTRAINT "userToGroup_groupId_fkey" FOREIGN KEY ("groupId")
      REFERENCES tom_web_portal_dev.groups (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT "userToGroup_userId_fkey" FOREIGN KEY ("userId")
      REFERENCES tom_web_portal_dev.users (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE

What is actually happening?

In postgres the constraints are both 'restrict':

  CONSTRAINT "userToGroup_groupId_fkey" FOREIGN KEY ("groupId")
      REFERENCES tom_web_portal_dev.groups (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT "userToGroup_userId_fkey" FOREIGN KEY ("userId")
      REFERENCES tom_web_portal_dev.users (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT

If I switch the order:

// do not allow a group to be deleted if it has references to users
Group.belongsToMany(User, {through: 'userToGroup', onDelete: 'RESTRICT'});
User.belongsToMany(Group, {through: 'userToGroup', onDelete: 'CASCADE'});

then it results in:

  CONSTRAINT "userToGroup_groupId_fkey" FOREIGN KEY ("groupId")
      REFERENCES tom_web_portal_dev.groups (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "userToGroup_userId_fkey" FOREIGN KEY ("userId")
      REFERENCES tom_web_portal_dev.users (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE

so it look like the latest value takes precedence.

Dialect: postgres
Database version: 9.1.20
Sequelize version: 3.23.0

[tjenkinson]

I've found this issue which is:

  // remove any PKs previously defined by sequelize
   _.each(this.through.model.rawAttributes, function(attribute, attributeName) {
    if (attribute.primaryKey === true && attribute._autoGenerated === true) {
      delete self.through.model.rawAttributes[attributeName];
      self.primaryKeyDeleted = true;
    }
  });

in https://github.com/sequelize/sequelize/blob/master/lib/associations/belongs-to-many.js.

The second time this runs it wipes the keys from the previous run (which contained the onDelete and onUpdate properties).

[mickhansen]

@tjenkinson That code only deals with removing an autogenerated id prop, not foreign key/composite primary keys, so odd that that sohuld be the issue.

[tjenkinson]

@mickhansen the contents of self.through.model.rawAttributes[attributeName] is like:

{ _autoGenerated: true,
     type:
      { options: [Object],
        _length: undefined,
        _zerofill: undefined,
        _decimals: undefined,
        _precision: undefined,
        _scale: undefined,
        _unsigned: undefined },
     unique: 'userToGroup_groupId_userId_unique',
     references: { model: [Object], key: 'id' },
     Model: userToGroup,
     fieldName: 'groupId',
     _modelAttribute: true,
     field: 'groupId',
     onDelete: 'RESTRICT',
     onUpdate: 'CASCADE' } }

So they get lost and then later on replaced with default values.

[tjenkinson]

Actually yes I see now that doesn't have primaryKey! I'll check a bit more.

[tjenkinson]

I think they are becoming primary keys at:

if (this.primaryKeyDeleted === true) {
    targetAttribute.primaryKey = sourceAttribute.primaryKey = true;
  } else if (this.through.unique !== false) {

If I do:

  _.each(this.through.model.rawAttributes, function(attribute, attributeName) {
    if (attribute.primaryKey === true && attribute._autoGenerated === true) {
      console.log("DELETING", attributeName, attribute);
      delete self.through.model.rawAttributes[attributeName];
      self.primaryKeyDeleted = true;
    }
  });

DELETING id { type:
   { options: { length: undefined },
     _length: undefined,
     _zerofill: undefined,
     _decimals: undefined,
     _precision: undefined,
     _scale: undefined,
     _unsigned: undefined },
  allowNull: false,
  primaryKey: true,
  autoIncrement: true,
  _autoGenerated: true,
  Model: userToGroup,
  fieldName: 'id',
  _modelAttribute: true,
  field: 'id' }
DELETING userId { _autoGenerated: true,
  type:
   { options: { length: undefined },
     _length: undefined,
     _zerofill: undefined,
     _decimals: undefined,
     _precision: undefined,
     _scale: undefined,
     _unsigned: undefined },
  primaryKey: true,
  references:
   { model:
      { tableName: 'users',
        table: 'users',
        name: 'user',
        schema: 'tom_web_portal_dev',
        delimiter: '.',
        toString: [Function] },
     key: 'id' },
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
  Model: userToGroup,
  fieldName: 'userId',
  _modelAttribute: true,
  field: 'userId' }
DELETING groupId { _autoGenerated: true,
  type:
   { options: { length: undefined },
     _length: undefined,
     _zerofill: undefined,
     _decimals: undefined,
     _precision: undefined,
     _scale: undefined,
     _unsigned: undefined },
  primaryKey: true,
  references:
   { model:
      { tableName: 'groups',
        table: 'groups',
        name: 'group',
        schema: 'tom_web_portal_dev',
        delimiter: '.',
        toString: [Function] },
     key: 'id' },
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
  Model: userToGroup,
  fieldName: 'groupId',
  _modelAttribute: true,
  field: 'groupId' }

[tjenkinson]

The first time it runs, it'd removing the id field, and then making user_id and group_id both part of a primary key.

This means then the second time it runs they both then get deleted.

[tjenkinson]

hey @mickhansen I think this should fix it:
#5871

[jsina]

but when I wanna add new data to data base with exact foreign keys but with another attributes value,
I have a User model with some attributes and a self reference with itself.

 user.belongsToMany(user, {
      as: 'TestInvite',
      otherKey: 'InvitedUserId',
      through: models.TestInvites,
    });

and TestInvite is:

export default function(sequelize, DataTypes) {
  const testInvites = sequelize.define('TestInvites', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    testType: {
      type: DataTypes.ENUM,
      values: [
        'MBTI',
        'NEO',
        'STROOP_COLOR',
        'IMAGE_VORTEX',
        'LOST_IN_MIGRATION',
        'SPEED_MATCH',
        'CHALK_BOARD_CHALLENGE',
      ],
    },
  },
  {
    paranoid: true,
    freezeTableName: true,
  });
...

the problem is when I want to add new TestInvites with new `testType' value, the previous data with exact foreign keys are being updated and in fact I want to have the previous one.
how could I fix this problem?