sequelize / sequelize/sequelize

Creating a new table in migration creates an extra index automatically

Open
#8,684 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

status: awaiting response
Dominant language
TypeScript
Stars
30.4k
Forks
4.3k
Avg merge
1d 6h
Merged PRs (30d)
68

Description

What are you doing?

Creating a table.

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('gloss_gloss', {
      glossId: {
        primaryKey: true,
        allowNull: false,
        type: Sequelize.INTEGER,
        references: {model: 'glosses', key: 'id'},
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      alt_glossId: {
        primaryKey: true,
        allowNull: false,
        type: Sequelize.INTEGER,
        references: {model: 'glosses', key: 'id'},
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('gloss_gloss');
  }
};

What do you expect to happen?

I wanted the table to have a primary key with no other key/indices.

What is actually happening?

The table has an extra index I never specified.

Dialect: MySQL

DROP TABLE IF EXISTS `gloss_gloss`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `gloss_gloss` (
  `glossId` int(11) NOT NULL,
  `alt_glossId` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`glossId`,`alt_glossId`),
  KEY `alt_glossId` (`alt_glossId`),
  CONSTRAINT `gloss_gloss_ibfk_1` FOREIGN KEY (`glossId`) REFERENCES `glosses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `gloss_gloss_ibfk_2` FOREIGN KEY (`alt_glossId`) REFERENCES `glosses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Specifically, I'm seeing KEY `alt_glossId` (`alt_glossId`), which I don't think should happen.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the supplied queryInterface.createTable migration against MySQL and inspect the generated CREATE TABLE statement. Trace the createTable handling from that entry point to determine why the foreign-key column receives an index; done means the composite primary key remains while the unspecified alt_glossId KEY is absent.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, mysql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.