TypeORM composite foreign key

Question

In a first class, the primary key is composite:

@Entity({
  name: 'ClassA',
  database: Constants.DATABASE_NAME,
})
export class ClassA {
  @PrimaryColumn({
    name: 'Field1',
    type: 'varchar',
    length: 4,
  })
  field1: string;

  @PrimaryColumn({
    name: 'Field2',
    type: 'varchar',
    length: 2,
  })
  field2: string;

  @PrimaryColumn({
    name: 'Field3',
    type: 'integer',
  })
  field3: number;

  @OneToMany(() => ClassB, (classB) => classB.classA)
  classB: ClassB[];
}

In another class, the first class is used as a foreign key:

@Entity({
  name: 'ClassB',
  database: Constants.DATABASE_NAME,
})
export class ClassB {

  ...

  @ManyToOne(() => ClassA, (classA) => classA.classB, {
    cascade: true,
  })
  @JoinColumn([
    { name: 'Field1', referencedColumnName: 'field1' },
    { name: 'Field2', referencedColumnName: 'field2' },
    { name: 'Field3', referencedColumnName: 'field3' },
  ])
  classA: ClassA;
}

When I start NestJS using nest start, I get the error

code: "ER_FK_NO_INDEX_PARENT"
errno: 1822
sqlMessage: Failed to add the foreign key constraint. Missing index for constraint 'FK_1d19fe001872b5ee9ab545c18f8' in the referenced table 'ClassA'
sqlState: "HY000

I tried to alter the ClassA table and add indexes on each column of the primary key, and then an index of all columns of the primary key, but the error is still the same.

Is this possible to manage this case with TypeORM?

Relevant packages:

• typeorm: 0.2.41
• @nestjs/core: 8.0.0
• @nestjs/typeorm: 8.0.3
• mysql: 2.18.1

DB: MySQL v8.0 in a Docker container (mysql:latest)

Thank you.

Edit

It seems that the issue is that the table is created in more than one step with TypeORM. The table looks like this after the error message.

CREATE TABLE `ClassB` (
  `Date` datetime NOT NULL,
  `ClassAField1` varchar(4) NOT NULL,
  `ClassAField2` varchar(2) NOT NULL,
  `ClassAField3` int NOT NULL,
  PRIMARY KEY (`Date`,`ClassAField1`,`ClassAField2`,`ClassAField3`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The error happens on the following query, where TypeORM tries to add the foreign key:

ALTER TABLE `ClassB`
ADD CONSTRAINT `FK_479eaf779e0d5f0d0a83bb9e30d`
FOREIGN KEY (`ClassAField1`, `ClassAField2`, `ClassAField3`)
REFERENCES `LandPlots`(`Field1`,`Field2`,`Field3`) ON DELETE NO ACTION ON UPDATE NO ACTION

I made a few tests, and the following query works as intended:

CREATE TABLE `ClassB` (
  `Date` datetime NOT NULL,
  `ClassAField1` varchar(4) NOT NULL,
  `ClassAField2` varchar(2) NOT NULL,
  `ClassAField3` int NOT NULL,
  PRIMARY KEY (`Date`),
  KEY `FK_e6b2926df2c758d8d8810e4345a` (`ClassAField1`, `ClassAField2`, `ClassAField3`),
  CONSTRAINT `FK_e6b2926df2c758d8d8810e4345a`
  FOREIGN KEY (`ClassAField1`, `ClassAField2`, `ClassAField3`)
  REFERENCES `ClassA` (`Field1`, `Field2`, `Field3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The difference seems to be the line KEY `FK_e6b2926df2c758d8d8810e4345a` (`ClassAField1`, `ClassAField2`, `ClassAField3`),, which is missing from the generated SQL by TypeORM.

From MySQL documentation, the KEY statement is synonymous to INDEX:

 KEY | INDEX

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems. 

So I tried to add the @Index decorator to the column but it did not change anything. I added logging of all executed queries, and it seems that the @Index decorator is not executed, so, maybe the @JoinColumn decorator has priority over it? (Tried to sort decorators different ways just in case).

The only solution that I can think of with TypeORM would be to generate a hash or concatenation of all 3 primary keys, and make it the sole primary key. Two drawbacks: Data duplication and CPU usage when parsing entities before insertion.

If someone has a better suggestion I would like to hear it.

Edit 2 following questions from @RickJames

Update statement:

UPDATE cb
SET cb.SomeField = 'NewValue'
FROM ClassB cb
JOIN ClassA ca
ON cb.Field1 = ca.Field1
AND cb.Field2 = ca.Field2
AND cb.Field3 = ca.Field3
WHERE ca.SomeOtherField LIKE '%partialvalue%'
AND cb.otherPK = 1

Answer 1

That UPDATE might benefit from some of these indexes:

cb:  INDEX(otherPK, Field1, Field2, Field3)
ca:  INDEX(Field1, Field2, Field3, SomeOtherField)