Insert fails when related OneToOne entity's primary key is also a foreign key #2758
Description
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.