Insert fails when related OneToOne entity's primary key is also a foreign key

[yazshel]

Issue type:

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

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

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

TypeORM fails to set the foreign key value on insert when the related entity's primary key is also part of a foreign key.

I have a three level model hierarchy which models user, person and party. Nested inserts The relationships look like User -> Person -> Party, however the relationship between person and party is somewhat unusual - it's a @OneToOne relationship where the primary key of Person is also a foreign key to Party.

Useralso has aOneToOnerelationship toPerson` but with a normal foreign key.

With this arrangement, Creating a new Person with a nested Party works as expected, but attempting to create a User with nested Person and Party records fails to set the User.personId foreign key field to the inserted person's ID.

Further, when attempting to create a new User and providing an existing Person object as User.person value, the same issue occurs.

Please see the minimal example below:

entity/entities.ts:

import { Column, Entity, JoinColumn, OneToOne, PrimaryColumn, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Party {
  @PrimaryGeneratedColumn('uuid')
  id: string;
}

@Entity()
export class Person {

  // Party ID also acts as PK for Person (ie. inheritance)
  @PrimaryColumn('uuid')
  id: string;

  @OneToOne(() => Party, { cascade: true, onDelete: "CASCADE" })
  @JoinColumn({ name: 'id' })
  party: Party;
}


@Entity()
export class User {

  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column('uuid')
  personId: string;

  @OneToOne(() => Person, { cascade: true, onDelete: "CASCADE" })
  person: Person;
}

index.ts:

import 'reflect-metadata';

import { createConnection } from 'typeorm';

import { Person, User } from './entity/entities';

createConnection().then(async connection => {

    const personRepository = connection.getRepository(Person);
    const userRepository = connection.getRepository(User);
    
    try {
        // Insert person with nested party: passes
        console.log("Test scenario 1: inserting person with nested new party");

        await connection.manager.save(personRepository.create({
            party: { },
        }));

        console.log("Test scenario 1 passed");
    } catch (ex) {
        console.log('Test scenario 1 failed: unable to insert person with new nested party')
    }

    try {
        // Insert user with nested person & party: fails with:
        // 'error: null value in column "personId" violates not-null constraint'
        console.log('Test scenario 2: inserting user with nested new person')

        await connection.manager.save(userRepository.create({
            person: { party: { } },
        }))

        console.log("Test scenario 2 passed");
    } catch (ex) {
        console.log('Test scenario 2 failed: unable to insert user with new nested person')
    }

    try {
        // Insert user with existing person: fails with:
        // 'error: null value in column "personId" violates not-null constraint'
        console.log("Test scenario 3: Inserting a new user with existing person...");
        
        const person = await connection.manager.save(personRepository.create({
            party: { }
        }));
        
        await connection.manager.save(userRepository.create({
            person: person,
        }));
 
        console.log("Test scenario 3 passed");
    } catch (ex) {
        console.log('Test scenario 3 failed: unable to insert user with existing person')
    }

    try {
        // Insert user with existing person ID: succeeds
        console.log('Test scenario 4: insert user with existing personId')
        
        const person = await connection.manager.save(personRepository.create({
            party: { },
        }));

        await connection.manager.save(userRepository.create({
            personId: person.id,
        }));
    
        console.log("Test scenario 4 passed");
    } catch (ex) {
        console.log('Test scenario 4 failed: unable to insert user with existing personId')
    }

    // Done
    
}).catch(error => console.log(error));

Test 1 simply creates a person with a nested party, which works as expected.
Test 2 attempts to create a user with nested person and party values; this fails with the below error.
Test 3 attempts to create a user with an existing person relation assigned to the person @OneToOne relationship property. This also fails with the same error.
Test 4 attempts to create a user with an existing person.id value in its personId foreign key field directly. This works as expected, and is how I am working around this issue for now.

The returned error message when saving a new User record with nested Person and Party is:

...
Test scenario 2: inserting user with nested new person
query: START TRANSACTION
query: INSERT INTO "party"("id") VALUES (DEFAULT) RETURNING "id"
query: INSERT INTO "person"("id") VALUES ($1) -- PARAMETERS: ["a2f9a16a-24ec-4015-81e2-880840c129b4"]
query: INSERT INTO "user"("id", "personId") VALUES (DEFAULT, DEFAULT) RETURNING "id"
query failed: INSERT INTO "user"("id", "personId") VALUES (DEFAULT, DEFAULT) RETURNING "id"
error: { error: null value in column "personId" violates not-null constraint
    at Connection.parseE (/Users/timshel/Development/Fluent/typeorm-composite-foreign-key-cascade-test/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/Users/timshel/Development/Fluent/typeorm-composite-foreign-key-cascade-test/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/Users/timshel/Development/Fluent/typeorm-composite-foreign-key-cascade-test/node_modules/pg/lib/connection.js:119:22)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:597:20)
  name: 'error',
  length: 215,
  severity: 'ERROR',
  code: '23502',
  detail: 'Failing row contains (8db91911-6d05-46f9-99ed-a18b5020b866, null).',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'user',
  column: 'personId',
  dataType: undefined,
  constraint: undefined,
  file: 'execMain.c',
  line: '2008',
  routine: 'ExecConstraints' }
query: ROLLBACK
Test scenario 2 failed: unable to insert user with new nested person

I've also tried changing the User.person relationship to a @ManyToOne relationship instead of @OneToOne). With this change test scenario 3 - providing an existing Person when creating a User - succeeds, however creating a User with a nested party and person (ie. test scenario 2) still fails as above.

[Nosfistis]

This occurs in any One-to-One relationship. It seems that the @JoinColumn() is not taken into account when deciding the insert order, and the entity containing it is being persisted first.

[MardariG]

Is there a milestone for this?

[IRCraziestTaxi]

To add to this, I created this repro for another issue that I discovered was my fault for misusing the @JoinColumn decorator before I found this issue and found that even with proper usage of the decorator, this error occurs on cascade insert.

I discovered, however, that not only cascade insert, but cascade delete also does not work.

At a glance, here are the relationships:

import { Column, Entity, OneToOne, PrimaryGeneratedColumn } from "typeorm";
import { UserProfile } from "./UserProfile";

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

    @OneToOne(() => UserProfile, {
        cascade: true,
        onDelete: "CASCADE",
        primary: true
    })
    public profile: UserProfile;

    @Column({
        length: 100,
        nullable: false,
        unique: true
    })
    public username: string;
}

import { nameof } from "ts-simple-nameof";
import { Column, Entity, JoinColumn, OneToOne, PrimaryColumn } from "typeorm";
import { User } from "./User";

@Entity()
export class UserProfile {
    @Column({
        length: 100,
        nullable: false,
        unique: true
    })
    public email: string;

    @PrimaryColumn()
    public id: number;

    @Column({
        length: 100,
        nullable: false
    })
    public name: string;

    @OneToOne(() => User)
    @JoinColumn({
        name: nameof<UserProfile>(p => p.id),
        referencedColumnName: nameof<User>(u => u.id)
    })
    public user: User;
}

Here is the code that produced the error addressed in this issue:

import { ConnectionOptions, createConnection } from "typeorm";
import { LinqRepository } from "typeorm-linq-repository";
import * as dbConfig from "../ormconfig.json";
import { User } from "./entities/User";
import { UserProfile } from "./entities/UserProfile";

async function main(): Promise<void> {
    const profile = new UserProfile();
    profile.email = `${Date.now()}@ex.com`;
    profile.name = "John Doe";

    const user = new User();
    user.profile = profile;
    user.username = `user${Date.now()}`;

    const createdUser = await new LinqRepository(User).create(user);

    // Error: null value in column "id" violates not-null constraint
}

So I tinkered with staggered creation followed by testing cascade delete:

(This is not checked in on the repro)

import { ConnectionOptions, createConnection } from "typeorm";
import { LinqRepository } from "typeorm-linq-repository";
import * as dbConfig from "../ormconfig.json";
import { User } from "./entities/User";
import { UserProfile } from "./entities/UserProfile";

async function main(): Promise<void> {
    const profile = new UserProfile();
    profile.email = `${Date.now()}@ex.com`;
    profile.name = "John Doe";

    const user = new User();
    // user.profile = profile;
    user.username = `user${Date.now()}`;

    // const createdUser = await new LinqRepository(User).create(user);

    const userRepository = new LinqRepository(User);
    const profileRepository = new LinqRepository(UserProfile);

    const createdUser = await userRepository.create(user);

    profile.user = createdUser;

    const createdProfile = await profileRepository.create(profile);

    await userRepository.delete(createdUser);

    // Error: update or delete on table "user" violates foreign key constraint
    // "FK_f44d0cd18cfd80b0fed7806c3b7" on table "user_profile"
}

Should a separate issue be opened for that? Or, since it appears to be related to this one, should I just wait to see if the fix for this one fixes that as well?

[osdiab]

Another example:

From our donations website

@Entity()
export class Donation extends CommonBaseEntity {
  @ManyToOne((type) => User, (user) => user.donations, {
    onDelete: "RESTRICT",
  })
  public fromUser?: User;
  @ManyToOne((type) => Nonprofit, (nonprofit) => nonprofit.donations, {
    onDelete: "RESTRICT",
    nullable: false,
  })
  public toNonprofit?: Nonprofit;

  @Column({ type: "uuid" })
  public toNonprofitId!: Nonprofit["id"];

  /**
   * Nonprofit to pay donation out to, if not `toNonprofit`
   */
  @OneToMany(
    (type) => Nonprofit,
    (nonprofit) => nonprofit.donationsReceivedForOtherNonprofits,
    { onDelete: "RESTRICT" }
  )
  public payToNonprofit?: Nonprofit | null;

  @Column({ type: "uuid", nullable: true })
  public payToNonprofitId!: Nonprofit["id"] | null;

  /**
   * Registers that this donation should be paid to a different nonprofit than
   * `toNonprofit`
   */
  @ManyToOne(
    (type) => EligibleDonationRecipientNonprofit,
    ({ donations }) => donations,
    { nullable: true, onDelete: "RESTRICT" }
  )
  // using the actual nonprofits involved as a composite key
  @JoinColumn([
    { name: "payToNonprofitId", referencedColumnName: "payToNonprofitId" },
    { name: "toNonprofitId", referencedColumnName: "nonprofitId" },
  ])
  public donationRecipient?: EligibleDonationRecipientNonprofit | null;

  // ...
}

And this:

@Entity()
@Unique("UQ_eligibleRecipientNonprofitPair", [
  "nonprofitId",
  "payToNonprofitId",
])
export class EligibleDonationRecipientNonprofit extends CommonBaseEntity {
  /**
   * The nonprofit that is eligible to receive donations on behalf of
   * `nonprofit`
   */
  @ManyToOne(
    (type) => Nonprofit,
    (nonprofit) => nonprofit.mayReceiveDonationsOnBehalfOf,
    { onDelete: "RESTRICT" }
  )
  public payToNonprofit?: Nonprofit;

  /**
   * ID of the nonprofit eligible to receive donations on behalf of `nonprofit`
   */
  @Column({ type: "uuid" })
  public payToNonprofitId!: Nonprofit["id"] | null;

  /**
   * Nonprofit whose donations can be paid to the `payToNonprofit`
   */
  @ManyToOne((type) => Nonprofit, (nonprofit) => nonprofit.mayPayDonationsTo, {
    onDelete: "RESTRICT",
  })
  public nonprofit?: Nonprofit;

  /**
   * ID of the nonprofit whose donations can be paid to the `payToNonprofit`
   */
  @Column({ type: "uuid" })
  public nonprofitId!: Nonprofit["id"] | null;

  /**
   * Donations that exploit this relationship to redirect a donation
   */
  @OneToMany((type) => Donation, (donation) => donation.donationRecipient)
  public donations?: Donation[];
}

When we try to create a donation doing something like this, it will fail saying that toNonprofitId hasn't been provided

  const toNonprofit = entityManager.findOneOrFail(Nonprofit, {id: toNonprofitId});
  const donation = entityManager.create(Donation, {
    toNonprofit,
    // ...
  });
  return entityManager.save(donation);

But can fix it if we do this instead:

  const toNonprofit = entityManager.findOneOrFail(Nonprofit, {id: toNonprofitId});
  const donation = entityManager.create(Donation, {
    // toNonprofit <- if we uncomment this line, then it fails again
    // necessary to avoid https://github.com/typeorm/typeorm/issues/2758
    toNonprofitId,
    // ...
  });
  return entityManager.save(donation);  // but now this donation won't have the nonprofit instance :P

[ivanmartos]

issue still present in typeorm 0.2.33 for mysql driver

[Rainson12]

I have the same issue, typeorm fails with Field '....' doesn't have a default value even though the field is a foreign key and its set in the parent entity we are using mariadb though

[adhimaswaskitaa]

Same problem here, any updates ?

[rodobarcaaa]

Same problem with MYSQL 8.0, any updates about it?

[CosminCraciun]

Capacitor / sqlite driver has same issue in typeorm 0.3.11

[lpicchi]

@yazshel the relation between User and Person has no JoinColumn() defined

@Entity()
export class User {

  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column('uuid')
  personId: string;

  @OneToOne(() => Person, { cascade: true, onDelete: "CASCADE" })
+ @JoinColumn({ personId: 'id' })
  person: Person;
}

From the docs @JoinColumn which is required and must be set only on one side of the relation

[LarsEllefsen]

issue still present for typeorm 0.3.17 with better-sqlite3 driver 8.4.0 . Any eta at all on a fix for this? Big dealbreaker for me going forward with typeorm