Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] websql

TypeORM version:

[ ] latest
[x] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

The steps for reproduction are supplied in the unit test in the related pull request.

Hello,

thank you for the great work on typeorm so far!

The use case here is to have a many to many relation over a relation table (Comment) with additional properties (message) using two one-to-many relations.

+----------+       +-----------+
|Post      +       |Comment    |       +----------+
+----------+  1:x  +-----------+       |User      |
|id (PK)   | <---> |postId (PK)|  x:1  +----------+
|comments[]|       |userId (PK)| <---> |id (PK)   |
+----------+       |message    |       |comments[]|
                   +-----------+       +----------+

The primary key of Comment is a combination of the two foreign keys.

In this use case i want to use eager loading together with cascade "insert", "update" and "remove".

Here are the entity definitions:

@Entity()
export class Post {

    @PrimaryGeneratedColumn()
    id: number;

    @OneToMany(type => Comment, comment => comment.post, {cascade: ["insert", "update", "remove"], eager: true})
    comments: Comment[];

}

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @OneToMany(type => Comment, comment => comment.user)
    comments: Comment[];

}

@Entity()
export class Comment {

    @ManyToOne(type => User, user => user.comments, {primary: true})
    user: User;

    @ManyToOne(type => Post, post => post.comments, {primary: true})
    post: Post;

    @Column()
    message: string;

}

Cascade insert and update are working like a charm but when it comes to cascade remove it triggers the following error:

QueryFailedError: ER_BAD_NULL_ERROR: Column 'postId' cannot be null

The reason is that only the logic is implemented to detach a relation by setting its foreign key field to NULL. However there are situations where that is not working.

In my use case the foreign key is part of a primary key which indicates that the entity itself is a relation type entity. If the primary key of such entity is about to be broken it means that the entity has to be deleted instead.

The following code from OneToManySubjectBuilder.ts only implements setting NULL so far.

removedRelatedEntitySubject.changeMaps =  [{
    relation: relation.inverseRelation!,
    value: null
}];

At least in MySQL primary keys cannot be null:

ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Please note that the same problem occurs when the inverse side foreign key field is not nullable.

I suggest the following 3 solutions:

1. Check if the inverse side foreign key field is "primary" and if thats the case remove the entity instead of settings its foreign key to null. I have prepared a pull request including unit test for that against @next, since you mentioned in Feature: Delete orphaned rows #1665 that you have reworked the whole cascade stuff.

2. Add additional configuration options to configure the disired behaviour like suggested in Feature: Delete orphaned rows #1665.

3. If the situation above occurs dont execute set NULL and throw a proper error message since we know it cannot work.

Thanks,

Kristoph