Cascade doesn't work with nullable relationship

[davidbnk]

Issue Description

I feel I might be missing something but ManyToOne with cascade won't work for me if:

• Have an entity with to cascade ManyToOne relationships, one nullable and the other not
• Use a client side generated id for the nullable relationship

Expected Behavior

Should respect cascade and save both relationships

Actual Behavior

The nullable relationship cascade won't work and the main entity insert will throw an error: Cannot add or update a child row: a foreign key constraint fails

Steps to Reproduce

import "reflect-metadata";
import { createConnection } from "typeorm";
import { Product } from "./entity/Product";
import { Category } from "./entity/Category";
import { Video } from "./entity/Video";

(async function () {
  const connection = await createConnection();
  await connection.synchronize();

  const category = new Category();
  category.name = 'Category name';

  const video = new Video();
  video.id = 3;
  video.url = 'https://example.com/';

  const product = new Product();
  product.title = 'Product title';
  product.category = category;
  product.video = video;

  await connection.getRepository(Product).save(product);
  await connection.close();
})();

import { Column, Entity, ManyToOne, PrimaryGeneratedColumn } from "typeorm";
import { Category } from "./Category";
import { Video } from "./Video";

@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title!: string;

  @ManyToOne((type) => Video, (video) => video.products, {
    cascade: true,
    nullable: true,
  })
  video?: Video;

  @ManyToOne((type) => Category, (category) => category.products, {
    cascade: true,
    nullable: false,
  })
  category!: Category;
}

import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from "typeorm";
import { Product } from "./Product";

@Entity()
export class Category {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name!: string;

  @OneToMany((type) => Product, (product) => product.category)
  products!: Product[];
}

import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from "typeorm";
import { Product } from "./Product";

@Entity()
export class Video {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  url!: string;

  @OneToMany((type) => Product, (product) => product.video)
  products!: Product[];
}

query: SELECT `Video`.`id` AS `Video_id`, `Video`.`url` AS `Video_url` FROM `video` `Video` WHERE `Video`.`id` IN (?) -- PARAMETERS: [3]
query: START TRANSACTION
query: INSERT INTO `category`(`id`, `name`) VALUES (DEFAULT, ?) -- PARAMETERS: ["Category name"]
query: INSERT INTO `product`(`id`, `title`, `videoId`, `categoryId`) VALUES (DEFAULT, ?, ?, ?) -- PARAMETERS: ["Product title",3,1]
query failed: INSERT INTO `product`(`id`, `title`, `videoId`, `categoryId`) VALUES (DEFAULT, ?, ?, ?) -- PARAMETERS: ["Product title",3,1]
error: Error: Cannot add or update a child row: a foreign key constraint fails (`db`.`product`, CONSTRAINT `FK_9d88d1768fab22374261a10f029` FOREIGN KEY 
(`videoId`) REFERENCES `video` (`id`))
    at Packet.asError (C:\Users\David\Desktop\bug\node_modules\mysql2\lib\packets\packet.js:712:17)
    at Query.execute (C:\Users\David\Desktop\bug\node_modules\mysql2\lib\commands\command.js:28:26)
    at PoolConnection.handlePacket (C:\Users\David\Desktop\bug\node_modules\mysql2\lib\connection.js:425:32)
    at PacketParser.onPacket (C:\Users\David\Desktop\bug\node_modules\mysql2\lib\connection.js:75:12)
    at PacketParser.executeStart (C:\Users\David\Desktop\bug\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.<anonymous> (C:\Users\David\Desktop\bug\node_modules\mysql2\lib\connection.js:82:25)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:486:12)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9) {
  code: 'ER_NO_REFERENCED_ROW_2',
  errno: 1452,
  sqlState: '23000',
  sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`db`.`product`, CONSTRAINT `FK_9d88d1768fab22374261a10f029` FOREIGN KEY (`videoId`) REFERENCES `video` (`id`))'
}
query: ROLLBACK

My Environment

Dependency	Version
Operating System	Windows 10
Node.js version	14.15.0
Typescript version	3.3.3333
TypeORM version	0.2.29

Additional Context

The bug won't occur if:

• The client side generated id is removed (video.id = 3;)
• The client side generated id is kept but the relationship between Product and Category is removed

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[nebkat]

I might be wrong, but I think this is because the video isn't saved yet in the database. It is attempting to set videoId to 3 based on the object you gave it, and at that moment that doesn't exist. Cascade will work for DELETE/UPDATE, but has no effect on INSERT.

[davidbnk]

That was my assumption too, but if you remove the relationship between Product and Category, you can set an id to video and it will work fine (the video will get inserted with id 3 before product). I found this behavior strange because adding or removing a relationship shouldn't affect how the cascade of the other relationship functions

[demorose]

Having the same issue with postgres

[DanielBlytheCTO]

Having similar issue with postgres. Any update on this?

[pacop]

Same issue here with postgres. Any update on this?

[venuziano]

Same issue here using MySQL.

[romyha]

Also facing this issue with MySQL. Works as soon as nullable = false, but fails for nullable = true ManyToOne relations.