can't set ManyToOne columns as primary 0.3.0

[fixako]

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:

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

Steps to reproduce or a small repository showing the problem:

@ManyToOne(type => Type, { primary: true })
error: primary is not an available option anymore.

I cannot set a column primary for @manytoone columns. It worked for 0.2.9. Why this was removed?

[pleerock]

primary flag has been removed from relation decorators. Now if you want to make a relation primary you must define a primary column with the same name relation uses

just define a primary column you need, for example:

@PrimaryColumn()
typeId: number

@ManyToOne(() => Type) // you can also specify a primary column name, e.g. @JoinColumn({ name: "typeId" })
type: Type

This change was made to simplify lot of things and fix some serious bugs.

[vogler]

@pleerock following your example:

@Entity()
export class Order extends AutoMeta {
  @PrimaryColumn()
  id: string;
  // ...
  @OneToMany(() => Item, item => item.order, { cascade: true, eager: true })
  items: Item[]
}
@Entity()
export class Item {
  @ManyToOne(() => Order, order => order.items)
  order: Order;

  // Use orderId+productId as a composite primary key. PrimaryColumn decoration on ManyToOne directly does not work; need to list the resulting orderId explicitly.
  @PrimaryColumn()
  orderId: string;

  @PrimaryColumn()
  productId: string;
  // ...
}
  // somewhere else
  await dbm.save(Order, orders);

This works once on an empty database, but then fails with QueryFailedError: SQLITE_CONSTRAINT: NOT NULL constraint failed: item.orderId.
Is there some further setup needed? Why is it not null for the initial run?

[vogler]

Changing it to this:

@Entity()
export class Item {
  @ManyToOne(() => Order, order => order.items, { primary: true })
  order: Order;

  @PrimaryColumn()
  productId: string;
  // ...
}

made it work:

litecli> PRAGMA table_info(item)
+-----+---------------+---------+---------+------------+----+
| cid | name          | type    | notnull | dflt_value | pk |
+-----+---------------+---------+---------+------------+----+
| 0   | orderId       | varchar | 1       | <null>     | 1  |
| 1   | productId     | varchar | 1       | <null>     | 2  |
| 2   | name          | varchar | 1       | <null>     | 0  |
...

until I deleted the database - then this approach fails with QueryFailedError: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed.

[vogler]

Using the first approach, setting orderId explicitly, it fails with QueryFailedError: SQLITE_CONSTRAINT: UNIQUE constraint failed: item.productId, item.orderId.
I save the same orders with the same items, save should detect that the records are already there and not try to insert them.

[vegerot]

primary flag has been removed from relation decorators. Now if you want to make a relation primary you must define a primary column with the same name relation uses

just define a primary column you need, for example:

@PrimaryColumn()
typeId: number

@ManyToOne(() => Type) // you can also specify a primary column name, e.g. @JoinColumn({ name: "typeId" })
type: Type

This change was made to simplify lot of things and fix some serious bugs.

I believe he wanted the ManyToMany to BE a primary column. Which, btw, I am getting the same error in PostgreSQL

[rjborba]

I'm also interested on it
FK and PK should coexist on same column.

[imagine10255]

+1

[willrnch]

+1

[griebdaniel]

I experienced the same issue in the following scenario: when a table has composite primary keys you have to specify both in the @joincolumn.

@Entity()
export class PhaseDependency {
  @ManyToOne(type => Phase, {  onDelete: "CASCADE", onUpdate: "CASCADE", primary: true })
  @JoinColumn([{ name: 'phase', referencedColumnName: 'name' }, { name: 'product', referencedColumnName: 'product' }])
  phase: Phase;

  @ManyToOne(type => Phase, { onDelete: "CASCADE", onUpdate: "CASCADE", primary: true })
  @JoinColumn({ name: 'dependency' })
  dependency: string;
}

The issue is that Phase entity had composite primary key, thus I needed to change the second (dependency relation to this):

  @ManyToOne(type => Phase, { onDelete: "CASCADE", onUpdate: "CASCADE", primary: true })
  @JoinColumn([{ name: 'dependency', referencedColumnName: 'name' }, { name: 'productDependency', referencedColumnName: 'product' }])
  dependency: Phase;

[shroomist]

I'm in the same boat, it should be possible to do FK and PK on the same column, did you guys figure out in the end of the day on how to do that correctly in typeorm? the accepted solution seem to be reporting issues, was it fixed?
should I open a new issue since this was closed?

[fabienlege]

I don't understand : why is this issue closed when there is no solution to this ? @pleerock

[jongomes]

issue too

[JesusJimenezG]

You can add the @PrimaryColumn decorator after the @ManyToOne, or any other relational decorator where the @JoinColumn exists:

  @ManyToOne(() => EventLeaderboard, {
    cascade: false,
    eager: false,
    nullable: false,
    onDelete: 'CASCADE',
  })
  @JoinColumn({
    name: 'event_window_id',
    referencedColumnName: 'eventWindow',
    foreignKeyConstraintName: 'FK_event_leaderboard_entry_event_window_id',
  })
  @PrimaryColumn({
    name: 'event_window_id',
    type: 'varchar',
    length: 255,
    primaryKeyConstraintName: 'PK_event_leaderboard_entry_id',
  })
  leaderboard: EventLeaderboard;

  @ManyToOne(() => Team, (team) => team.eventLeaderboardEntry, {
    cascade: false,
    eager: false,
    nullable: false,
    onDelete: 'RESTRICT',
  })
  @JoinColumn({
    name: 'team_id',
    referencedColumnName: 'teamId',
    foreignKeyConstraintName: 'FK_event_leaderboard_entry_team_id',
  })
  @PrimaryColumn({
    name: 'team_id',
    type: 'text',
    primaryKeyConstraintName: 'PK_event_leaderboard_entry_id',
  })
  team: Team;

This is an example of composite primary keys, both @PrimaryColumn's primaryKeyConstraintName must have the same value/name for TypeORM to generate the correct SQL syntax.

Now, as for @griebdaniel, when referencing multiple columns as an array in the @JoinColumn, you need to explicitly define new @PrimaryColumn for each one because the @PrimaryColumn decorator doesn't support an array of columns as the @JoinColumn decorator does.

  @PrimaryColumn({
   name: 'event_window_id',
   type: 'varchar',
   length: 255,
   primaryKeyConstraintName: 'PK_event_session_id',
 })
 eventWindowId: string;

 @PrimaryColumn({
   name: 'team_id',
   type: 'text',
   primaryKeyConstraintName: 'PK_event_session_id',
 })
 teamId: string;

 @ManyToOne(() => EventLeaderboardEntry, {
   cascade: false,
   eager: false,
   nullable: false,
   onDelete: 'CASCADE',
 })
 @JoinColumn([
   {
     name: 'event_window_id',
     referencedColumnName: 'leaderboard',
     foreignKeyConstraintName: 'FK_event_session_event_window_id',
   },
   {
     name: 'team_id',
     referencedColumnName: 'team',
     foreignKeyConstraintName: 'FK_event_session_team_id',
   },
 ])
 eventLeaderboardEntry: EventLeaderboardEntry;

Although unnecessary in the business logic side, they're mainly used for TypeORM to create the correct syntax when generating SQL migrations.