ManyToOne relation without foreign key constraint possible?

[rudism]

Issue type:

[x] question
[ ] 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)

Steps to reproduce or a small repository showing the problem:

I am trying to establish a relationship between two entities like these:

@Entity()
class Relationship {
  @PrimaryColumn()
  entityAId!: string;

  @PrimaryColumn()
  entityBId!: string;

  // contains other columns pertaining to the relationship
  // between EntityA and EntityB
}

@Entity()
class EntityBInformation {
  @PrimaryColumn()
  entityBId!: string;

  // contains other columns pertaining specifically to EntityB
}

There may or may not be an entry in EntityBInformation for an entityBId that appears in the Relationship table, and the Relationship table could contain multiple rows with the same entityBId. My goal is to make it so that I can either lazy or eager load the appropriate EntityBInformation into Relationship, leaving it null if there isn't one for that entityBId.

When I set up a typical ManyToOne and OneToMany relation between the tables like this:

class Relationship {
  ...
  @ManyToOne(type => EntityBInformation)
  @JoinColumn({ name: 'entityBId' })
  entityBInformation?: EntityBInformation;
}

This creates a foreign key constraint from the Relationship table to the EntityBInformation table, which is not what I want because then in order for the Relationship to exist there must be an EntityBInformation row that corresponds first. My goal is to have this property loadable through the repository API, but without that foreign key restraint so that entityBInformation could be null if there's no corresponding row to populate it.

Am I barking up the wrong tree trying to define a relation here? The only other option I can see is to create the entityBInformation property on Relationship and then manually do leftJoinAndMapOne with the query builder every time (losing some of the convenience of the repository API).

[Kononnable]

I'm not sure I understand what is exactly your problem. You don't want to create FK constraint but FKs don't always enforce existence of a corresponding rows. It should work as you like if column is nullable.

[rudism]

The problem is that I want to be able to put a value in the column that doesn't necessarily have a corresponding row in the foreign table, which wouldn't be permitted by a foreign key constraint. As a (lame) real-life example, imagine tracking a one-to-many relationship between people and their favorite colors:

table favorite_colors

personId (PK)	colorIdentifier (PK)	reason
1	YELLOW	reminds him of the sun
1	RED	he likes apples
2	BLUE	reminds her of the sea
2	RED	favorite type of wine
3	GREEN	loves broccoli
3	FUCHSIA	likes the way it sounds

table color_notes

colorIdentifier (PK)	notes
YELLOW	lemons are this color
ORANGE	oranges are this color
FUCHSIA	nobody real likes this color

The idea would be to define the relationship between favorite_colors.colorIdentifier and color_notes.colorIdentifier without having a foreign key constraint defined in favorite_colors that references color_notes, since that would prevent the rows in favorite_colors existing for colors that didn't have any notes in the color_notes table.

[vlapo]

We already have a feature request about disabling FK option. Closing this issue in favour of #3120.

[JPerkan]

In my case it worked for me to add synchronization = false to the entity header.
@Entity({name:'cashRegister_deposit', synchronize:false})

[LachlanStuart]

@JPerkan (and anyone else who came here via Google, as this issue seems to have a better search ranking than the one with the fix): Foreign keys can be directly disabled on individual relations per the comment here: #3120 (comment)

First available in version 0.2.31 (2021-02-08)

[seriousManual]

@LachlanStuart I come from google and I very much appreciate your comment. 🙏

[gitSambhal]

https://typeorm.io/relations-faq#avoid-foreign-key-constraint-creation