Search code examples
mysqlnode.jstypescriptormtypeorm

Cannot create a OneToOne relationship

I'm using Typeorm and I'm trying to create a OneToOne relationship between two entities: Project and Resource. For doing so I did:

import { Entity, Column, PrimaryGeneratedColumn, OneToMany, OneToOne, JoinColumn, PrimaryColumn } from 'typeorm';
import { Customer } from './customer';

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

    @Column({ nullable: false })
    public name!: string;

    @Column({ nullable: true })
    public description!: string;
    
    @PrimaryColumn({ nullable: false })
    public customer_id!: number;

    @OneToOne(() => Customer)
    @JoinColumn({ name: 'customer_id' })
    public customer!: Customer;

    @Column({ default: false })
    public deleted: boolean = false;
}

then I have Resource entity:

import { Entity, Column, PrimaryGeneratedColumn, OneToOne, JoinColumn, PrimaryColumn } from 'typeorm';
import { Project } from './project';

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

    @Column({ nullable: false })
    public name!: string;

    @Column()
    public description: string = "";

    @Column()
    public deleted: boolean = false;

    @PrimaryColumn({ nullable: false })
    public project_id!: number;

    @OneToOne(() => Project)
    @JoinColumn({ name: 'project_id' })
    public project!: Project;
}

Essentially a Resource can be associated to one Project, so I've defined the OneToOne decorator above the project property of Resource, and then, I've also defined a column called project_id, I need this columns for my API design.

Usually Typeorm create the projectId column, so I've specified with name property: project_id, the problem's that I got:

message: "ER_DUP_FIELDNAME: Duplicate column name 'project_id'", code: 'ER_DUP_FIELDNAME', errno: 1060, sqlMessage: "Duplicate column name 'project_id'", sqlState: '42S21', index: 0, sql: 'CREATE UNIQUE INDEX REL_1c2e17cbbe9905b63f1f870679 ON resource (project_id, project_id)', name: 'QueryFailedError', query: 'CREATE UNIQUE INDEX REL_1c2e17cbbe9905b63f1f870679 ON resource (project_id, project_id)', parameters: [] }

I was able to fix this using:

@JoinColumn({ name: 'project_id', referencedColumnName: 'id' })

but I don't understand why in the first setup this didn't worked 'cause I did exactly the same for Project entity, in particular linking the Customer table and it works.

Also, I'm not sure that this is correct, these are the constraints created:

enter image description here

and this is the ER so far:

enter image description here

Solution

  • It didn't worked because you have a field named project_id (the @PrimaryColumn) and another field named project with the name option set to project_id. So as you can see TypeORM is trying to create two fields with the same name. This is the origin of the error.

    You can remove the field public project_id!: number; since TypeORM loads the relation project!: Project; automatically (only the id or the entire entity if you want) so it's useless.

    If you really want the relation id then use the @RelationId decorator.

    Example below:

    import { Entity, Column, PrimaryGeneratedColumn, OneToOne, JoinColumn, PrimaryColumn, RelationId } from 'typeorm';
import { Project } from './project';

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

    @Column({ nullable: false })
    public name!: string;

    @Column()
    public description: string = "";

    @Column()
    public deleted: boolean = false;

    // CHANGES HERE
    @RelationId((resource: Resource) => resource.project)
    public project_id!: number;

    @OneToOne(() => Project)
    @JoinColumn()
    public project!: Project;
}

    Hope it helps :)