Foreign key reference a column in the same table causes error

[aankur]

What are you doing?

module.exports = (sequelize, DataTypes) => {
  class Test extends sequelize.Sequelize.Model { }

  Test.init({
    id: {
      field: 'id',
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.BIGINT(11)
    },
    userId1: {
      field: 'user_id_1',
      allowNull: false,
      type: DataTypes.STRING(36),

    },
    userId2: {
      field: 'user_id_2',
      allowNull: false,
      type: DataTypes.STRING(64),
    },
  }, { indexes: [
      { unique: true, fields: [ 'user_id_1' ]},
      { fields: ['user_id_2' ]}
    ],
    underscored: true,
    sequelize, modelName: 'Test' });

  Test.associate = ({ Test }) => {
    Test.hasMany(Test, { sourceKey:'userId2', targetKey: 'userId1', foreignKey: 'user_id_2' });
  };

  return Test;
};

To Reproduce
Steps to reproduce the behavior:
Run Test.sync()

What do you expect to happen?

CREATE TABLE `tests` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id_1` varchar(36) NOT NULL,
  `user_id_2` varchar(64) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tests_user_id_1` (`user_id_1`),
  KEY `tests_user_id_2` (`user_id_2`),
  CONSTRAINT `fk_userid1_userid2` FOREIGN KEY (`user_id_2`) REFERENCES `tests` (`user_id_1`)
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

What is actually happening?

CREATE TABLE IF NOT EXISTS `tests` (
  `id` BIGINT(11) NOT NULL auto_increment ,
  `user_id_1` VARCHAR(36) NOT NULL, 
  `user_id_2` VARCHAR(36),
  `created_at` DATETIME NOT NULL, 
  `updated_at` DATETIME NOT NULL, 
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id_2`) REFERENCES `tests` (`user_id_1`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

Unhandled rejection SequelizeDatabaseError: Can't create table `test_bug`.`tests` (errno: 150 "Foreign key constraint is incorrectly formed")

Environment

Dialect:

• mysql
  Dialect library version: mysql2: "^1.6.5"
  Database version: '10.1.13-MariaDB'
  Sequelize version: 5.10.1
  Node Version: 8.9.1
  OS: windows

[papb]

Can you please test this with another dialect? This is important to know whether the problem is dialect-specific or not.

You can instantiate an in-memory SQLite instance very easily:

1. npm install sqlite3
2. new Sequelize('sqlite::memory:')

You can instantiate a local postgres instance very easily:

1. Run a local postgres
   • On windows: PostgreSQLPortable_10.4.1.zip (no need to install, just unzip and run)
     • Run the .exe and then CREATE DATABASE testdb; (don't close the .exe)
   • On linux (with docker): docker pull postgres && docker run --rm --name pg -d -p 5432:5432 postgres
     • Run docker exec -t pg psql -U postgres -c 'CREATE DATABASE testdb;'
2. new Sequelize('postgres://postgres@localhost:5432/testdb')

[aankur]

Hi,

i have tested as suggested, here is the response.
1. sqlite3, no error

Test.associate = ({ Test }) => {
    Test.hasMany(Test, { sourceKey:'userId2', targetKey: 'userId1', foreignKey: 'user_id_2' });
  };

Executing (default): CREATE TABLE IF NOT EXISTS `tests` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `user_id_1` VARCHAR(36) NOT NULL, `user_id_2` VARCHAR(64) REFERENCES `tests` (`user_id_2
`) ON DELETE SET NULL ON UPDATE CASCADE, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`tests`)
Executing (default): PRAGMA INDEX_INFO(`tests_user_id_1`)
Executing (default): PRAGMA INDEX_INFO(`tests_user_id_2`)

Test.associate = ({ Test }) => {
    Test.hasMany(Test, { sourceKey:'userId1', targetKey: 'userId2', foreignKey: 'user_id_2' });
};

Executing (default): CREATE TABLE IF NOT EXISTS `tests` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `user_id_1` VARCHAR(36) NOT NULL, `user_id_2` VARCHAR(36) REFERENCES `tests` (`user_id_1
`) ON DELETE SET NULL ON UPDATE CASCADE, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`tests`)
Executing (default): PRAGMA INDEX_INFO(`tests_user_id_1`)
Executing (default): PRAGMA INDEX_INFO(`tests_user_id_2`)

2. Postgres

Test.associate = ({ Test }) => {
    Test.hasMany(Test, { sourceKey:'userId2', targetKey: 'userId1', foreignKey: 'user_id_2' });
  };

Executing (default): CREATE TABLE IF NOT EXISTS "tests" ("id"   BIGSERIAL , "user_id_1" VARCHAR(36) NOT NULL, "user_id_2" VARCHAR(64) REFERENCES "tests" ("user_id_2") ON DELETE SET NULL
 ON UPDATE CASCADE, "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Unhandled rejection SequelizeDatabaseError: there is no unique constraint matching given keys for referenced table "tests"

   Test.associate = ({ Test }) => {
    Test.hasMany(Test, { sourceKey:'userId1', targetKey: 'userId2', foreignKey: 'user_id_2' });
  };

Executing (default): CREATE TABLE IF NOT EXISTS "tests" ("id"   BIGSERIAL , "user_id_1" VARCHAR(36) NOT NULL, "user_id_2" VARCHAR(36) REFERENCES "tests" ("user_id_1") ON DELETE SET NULL
 ON UPDATE CASCADE, "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Unhandled rejection SequelizeDatabaseError: there is no unique constraint matching given keys for referenced table "tests"

[Jplus2]

any update on this?

[poode]

I just add { constraints: false } option like:
this.hasOne(this, { sourceKey: 'OrigNum', foreignKey: 'Num', as: 'Orig', constraints: false })
and everything work fine!

[carlos00027]

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂