Compound Keys

[ReneHollander]

I am using compound keys in my model. For example I have a city table which uses the postcode and name as a compound key:

@Table
class City extends Model<City> {

  @PrimaryKey
  @Column
  postcode: number;

  @PrimaryKey
  @Column
  name: string;

  @Default(false)
  @Column
  enabled: boolean;

}

export default City;

Which results in the following create which looks correct:

CREATE TABLE IF NOT EXISTS `City` (`postcode` INTEGER NOT NULL, `name` VARCHAR(255) NOT NULL, `enabled` TINYINT(1) DEFAULT 0, PRIMARY KEY (`postcode`, `name`));

I then want to reference this table from my user table:

@Table
class User extends Model<User> {

  @Unique
  @Column
  email: string;

  @Column
  address: string;

  @ForeignKey(() => City)
  @Column
  postcode: number;

  @ForeignKey(() => City)
  @Column
  cityName: string;

  @BelongsTo(() => City)
  city: City;

}

export default User;

But as you can see, no foreign key constraints are added to the table:

CREATE TABLE IF NOT EXISTS `User` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `email` VARCHAR(255) UNIQUE, `address` VARCHAR(255), `postcode` INTEGER, `cityName` VARCHAR(255), UNIQUE (`email`));

I am assuming I have to somehow reference the actual attributes in the other table from the user table, but I am not sure how to do it.

[RobinBuschmann]

Hey @ReneHollander, this is not working with associations only, you need to use scopes additionally. See here a discussion about it: https://github.com/sequelize/sequelize/issues/311

And here is the documentation of scopes in associations: http://docs.sequelizejs.com/manual/tutorial/associations.html#scopes

If you have issues getting this to work, just come back and I try to help you with this :)

[ReneHollander]

Thanks for getting back! I actually found that issue and with that I gave up and tried using typeorm. But typeorm is also not behaving correctly with composite keys. I will try it again with sequelize. (I also did not see the hint with the scopes...)

[WarienX]

Hey @ReneHollander, this is not working with associations only, you need to use scopes additionally. See here a discussion about it: sequelize/sequelize#311

And here is the documentation of scopes in associations: http://docs.sequelizejs.com/manual/tutorial/associations.html#scopes

If you have issues getting this to work, just come back and I try to help you with this :)

@RobinBuschmann, I am facing this same issue in Referencing a Foreign Key from one table to another having Composite Primary Key. According to what you suggested I tried using Scopes, but was still unable to get it working. Can you please share an example of how to link such table's using Scopes? Thanks much in advance.

This is the structure for Table Relation.