Insert or update on table violates foreign key constraint TelephoneContact

Question

Objective

Create inside the telephones table when I create the client.

What happening

I have 3 tables, called clients, contacts, and telephones. Following this image, we can see that the owner_id can be contacts.id and/or clients.id. But when I try to create the client, the client is created but not inserted data inside the telephones tables. But show this error

{
  "error": "insert or update on table \"telephones\" violates foreign key constraint \"TelephoneContact\""
}

Migration

import {
  MigrationInterface,
  QueryRunner,
  TableColumn,
  TableForeignKey,
} from 'typeorm';

export default class AddOwnerIdToTelephones1597250413640
  implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn(
      'telephones',
      new TableColumn({
        name: 'owner_id',
        type: 'uuid',
        isNullable: false,
      }),
    );

    await queryRunner.createForeignKey(
      'telephones',
      new TableForeignKey({
        name: 'TelephoneClient',
        columnNames: ['owner_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'clients',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
      }),
    );

    await queryRunner.createForeignKey(
      'telephones',
      new TableForeignKey({
        name: 'TelephoneContact',
        columnNames: ['owner_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'contacts',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
      }),
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropForeignKey('telephones', 'TelephoneContact');
    await queryRunner.dropForeignKey('telephones', 'TelephoneClient');
    await queryRunner.dropColumn('telephones', 'owner_id');
  }
}

Telephone Model

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

import Client from './Client';
import Contact from './Contact';

@Entity('telephones')
class Telephone {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  telephone_number: string;

  @ManyToOne(() => Client, client => client.id)
  @ManyToOne(() => Contact, contact => contact.id)
  @JoinColumn({ name: 'owner_id' })
  owner_id: string;
}

export default Telephone;

Answer 1

You can not create the telephones table as described. You are attempting the define the column owner_id as a FK to the contacts table and as a FK to the clients table. Postgres can not do this. The column can be a FK to either table but then must always be a FK to the specified table. I don't know how your ORM handles this but it looks like it results in an FK to contacts (actual table ddl would show definition). When you then insert into clients is gets an FK violation because the telephone value does not exist contacts.

There are 3 solutions:

1. Add 2 fk columns to in telephone, one to each table. You then have to decide if one of them must be null or can they both be populated.
2. Reverse the direction of FKs so clients and contacts both have a FK to telephone. Without great difficulty this implies they can both point to the same telephone.
3. Just migrate the telephone number to both clients and contacts tables and drop the telephone table altogether. After all what is the business purpose of separating out telephone numbers; how does the business deal with telephone numbers differently that emails.

Without significant business processes on telephone numbers themselves I would opt for #3.