13

Is it possible to reference composite primary keys in Sequelize?

I'm working on a web-app that helps organize kitchen waste. The restaurant organizes its weeks and months into 'periods' where the first week of September would be '9.1'. For every period, I need to create a new batch of ingredient objects that can keep track of what their prices and quantities were for that period. I figure it would be best to make the period primary keys their combined month and week, as that will be unique in the database. I may add year on later, but that doesn't change my problem.

The database I'm working with is Postgres.

This is my period table model in my sequelize seed file:

.then(() => queryInterface.createTable('periods', {
      month: {
        type: Sequelize.INTEGER,
        validate: {
          max: 12,
          min: 1
        },
        unique: "monthWeekConstraint",
        primaryKey: true
      },
      week: {
        type: Sequelize.INTEGER,
        validate: {
          max: 4,
          min: 1
        },
        unique: "monthWeekConstraint",
        primaryKey: true
      },
      createdAt: {
        type: Sequelize.DATE
      },
      updtedAt: {
        type: Sequelize.DATE
      }
    }))

I'd like to reference the periods stored in the above table in my periodItems table, which I have (incorrectly) looking like:

.then(() => queryInterface.createTable('periodItems', {
  periodMonth: {
    type: Sequelize.INTEGER,
    references: {model: 'periods', key: 'monthWeekConstraint'}
  },
  periodWeek: {
    type: Sequelize.INTEGER,
    references: {model: 'periods', key: 'monthWeekConstraint'}
  },
  day: {
    type: Sequelize.INTEGER,
    validate: {
      min: 1,
      max: 7
    }
  },
...other irrelevant fields...
}))

I'm definitely new to databases, so I apologize if I'm way off. I've gotten a few other tables doing what I'd like, but I've been stuck on this problem for a few days.

sleepy_pf
  • 143
  • 1
  • 1
  • 10

2 Answers2

20

While it is possible to create composite primary keys in Sequelize by specifying primaryKey: true against more than one column (as you have already done above), Sequelize doesn't currently support composite foreign keys, so there is no way to reference a model/table which has composite primary keys.

See https://github.com/sequelize/sequelize/issues/311 for a discussion on the subject.

Timshel
  • 1,653
  • 12
  • 9
  • is this still the case? – friartuck Feb 24 '21 at 02:59
  • 1
    Hi @Prof, I believe so, given the Github issue linked above is still open and active. AFAICT the only way around it is to use single primary key column on all target tables, and restrict yourself to using single column foreign keys. The downside is that this does not allow for many complex constraint enforcement scenarios that a composite foreign key would handle. – Timshel Feb 25 '21 at 03:12
  • 1
    ahh that is a shame. in the end I opted for a surrogate key with uniqueness over the foriegn keys. – friartuck Feb 27 '21 at 07:26
  • I wonder why would one want a composite foreign key?.. – x-yuri Apr 23 '21 at 23:32
  • 1
    @x-yuri I've used it frequently to ensure data integrity. This can reduce possible wrong associations that could result from other applications that uses the same database or from migrations. So if an application does something wrong, data won't be corrupted. But there might be other scenarios as well. – robsch Jun 02 '21 at 14:08
  • @robsch Could you possibly be more specific? Like, say, we have users and posts. And in posts we have a foreign key consisting of user_first_name and user_last_name, in place of user_id? Or user_id + user_first_name + user_last_name? Which doesn't sound good to me because they might point to different users, and first_name + last name is not necessarily unique. – x-yuri Jun 02 '21 at 21:19
  • @x-yuri I'd say in case of two tables it would't make sense. Have a look at [this](https://dba.stackexchange.com/q/188995/68063) or [that](https://stackoverflow.com/q/26078535/57091). I think it gets useful if you have a group of relate tables (models) where entries should not get mixed up. But it might be possible to avoid that - not sure, I'm not an db expert. – robsch Jun 04 '21 at 07:49
  • @robsch Have you possibly missed that the question is about composite _foreign_ keys, not composite _primary_ keys? Composite primary keys are okay in join tables. However, Ruby on Rails, for one, by default adds the `id` column to such tables (`has_many :through`) and makes it the primary key. And I'm not sure if it's easy to avoid that. But I don't see any significant downside to this. Some even say it may come in handy down the road... – x-yuri Jun 07 '21 at 06:37
  • ...As for composite foreign keys... I don't remember an ORM that supports them. And although theoretically they might be useful in some cases, I fail to come up with one. One of your links gives an idea for a case, but it's arguably easier done with a single column primary key. I'd say, if you need a composite foreign key, it's best to add an `id` column to the join table, make it the primary key, and reference it. – x-yuri Jun 07 '21 at 06:37
2
model/product.js:
const Product = sequelize.define("product", {
  sku: { type: Sequelize.STRING, allowNull: false, primaryKey: true },
  title: { type: Sequelize.STRING, allowNull: false },
  availability: {
    type: Sequelize.STRING,
    allowNull: false,
    defaultValue: false,
  }
});

model/Attribute.js:
const Attribute = sequelize.define("attribute", {
  key: { type: Sequelize.STRING, allowNull: false, primaryKey: true },
  productSku: { type: Sequelize.STRING, allowNull: false, primaryKey: true },
  value: { type: Sequelize.STRING, allowNull: false },
});


After importing to app.js:
product.hasMany(attribute, { foreignKey: "productSku", sourceKey: "sku" });
attribute.belongsTo(product, { foreignKey: "productSku", targetKey: "sku" });


Explanation:
Product.sku is exported as foreign key to Attibute.productSku. Attribute table has a composite foreign (key + productSku), and a ForeignKey(productSku) from product.sku;
Wellington Silva
  • 17
  • 1